Re: [GENERAL] Postgresql not using an index

2008/8/1 Marc Cuypers <m.cuypers@mgvd.be>:
> Hi Pavel,
>
> Pavel Stehule schreef:
>>
>> 2008/8/1 Marc Cuypers <m.cuypers@mgvd.be>:
>>>
>>> Hi Pavel,
>>>
>>> Isn't the text for the Verbose Explain analyze not enough?
>>> Is not, how can i generate it?
>>>
>>> --
>>
>>
>> no, I am missing statistics info
>>
>> try
>> EXPLAIN ANALYZE SELECT .....
>>
>> regards
>
> I entered the command in pgsql and got the following output:
>
> # explain analyze select * from file where jobid=2792;
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------
> Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual
> time=37738.780..90453.299 rows=1 loops=1)
> Filter: (jobid = 2792)
> Total runtime: 90453.419 ms
> (3 rows)
>
> Makes any sence?

your statistics are absolutly out - planner expect 207K rows but
currently resault is one row - try to run ANALYZE statement or
increase your statistics
http://www.postgresql.org/docs/8.0/interactive/sql-altertable.html
look ALTER TABLE SET STATISTICS

regards
Pavel
>
> --
> Marc
>
>>>
>>> Marc
>>>
>>> Pavel Stehule schreef:
>>>>
>>>> Hello
>>>>
>>>> please, send EXPLAIN ANALYZE output.
>>>>
>>>> regards
>>>> Pavel Stehule
>>>>
>>>> 2008/8/1 Marc Cuypers <m.cuypers@mgvd.be>:
>>>>>
>>>>> Hi,
>>>>>
>>>>> I'm using postgres 7.4 and bacula 1.38 on debian.
>>>>>
>>>>> In the bacula database there is a table named 'file' which has about
>>>>> 2.5
>>>>> million rows.
>>>>> In this table there is a field 'jobid' which is indexed.
>>>>> The index is created with the following command:
>>>>> CREATE INDEX file_jobid_idx ON file USING btree (jobid);
>>>>>
>>>>> The query:
>>>>> SELECT * from file where jobid=2792
>>>>>
>>>>> does a full scan and to my opinion it doesn't use the index.
>>>>> I already did a VACUUM ANALYZE on the database.
>>>>>
>>>>>
>>>>> Somebody an idea?
>>>>>
>>>>> EXPLAIN tells the following:
>>>>> Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual
>>>>> time=103215.145..161153.664 rows=1 loops=1)
>>>>> Filter: (jobid = 2792)
>>>>> Total runtime: 161154.734 ms
>>>>>
>>>>> The Verbose Explain tells the following:
>>>>> {SEQSCAN
>>>>> :startup_cost 0.00
>>>>> :total_cost 707683.30
>>>>> :plan_rows 207562
>>>>> :plan_width 110
>>>>> :targetlist (
>>>>> {TARGETENTRY
>>>>> :resdom
>>>>> {RESDOM
>>>>> :resno 1
>>>>> :restype 23
>>>>> :restypmod -1
>>>>> :resname fileid
>>>>> :ressortgroupref 0
>>>>> :resorigtbl 440806231
>>>>> :resorigcol 1
>>>>> :resjunk false
>>>>> }
>>>>>
>>>>> :expr
>>>>> {VAR
>>>>> :varno 1
>>>>> :varattno 1
>>>>> :vartype 23
>>>>> :vartypmod -1
>>>>> :varlevelsup 0
>>>>> :varnoold 1
>>>>> :varoattno 1
>>>>> }
>>>>> }
>>>>>
>>>>> {TARGETENTRY
>>>>> :resdom
>>>>> {RESDOM
>>>>> :resno 2
>>>>> :restype 23
>>>>> :restypmod -1
>>>>> :resname fileindex
>>>>> :ressortgroupref 0
>>>>> :resorigtbl 440806231
>>>>> :resorigcol 2
>>>>> :resjunk false
>>>>> }
>>>>>
>>>>> :expr
>>>>> {VAR
>>>>> :varno 1
>>>>> :varattno 2
>>>>> :vartype 23
>>>>> :vartypmod -1
>>>>> :varlevelsup 0
>>>>> :varnoold 1
>>>>> :varoattno 2
>>>>> }
>>>>> }
>>>>>
>>>>> {TARGETENTRY
>>>>> :resdom
>>>>> {RESDOM
>>>>> :resno 3
>>>>> :restype 23
>>>>> :restypmod -1
>>>>> :resname jobid
>>>>> :ressortgroupref 0
>>>>> :resorigtbl 440806231
>>>>> :resorigcol 3
>>>>> :resjunk false
>>>>> }
>>>>>
>>>>> :expr
>>>>> {VAR
>>>>> :varno 1
>>>>> :varattno 3
>>>>> :vartype 23
>>>>> :vartypmod -1
>>>>> :varlevelsup 0
>>>>> :varnoold 1
>>>>> :varoattno 3
>>>>> }
>>>>> }
>>>>>
>>>>> {TARGETENTRY
>>>>> :resdom
>>>>> {RESDOM
>>>>> :resno 4
>>>>> :restype 23
>>>>> :restypmod -1
>>>>> :resname pathid
>>>>> :ressortgroupref 0
>>>>> :resorigtbl 440806231
>>>>> :resorigcol 4
>>>>> :resjunk false
>>>>> }
>>>>>
>>>>> :expr
>>>>> {VAR
>>>>> :varno 1
>>>>> :varattno 4
>>>>> :vartype 23
>>>>> :vartypmod -1
>>>>> :varlevelsup 0
>>>>> :varnoold 1
>>>>> :varoattno 4
>>>>> }
>>>>> }
>>>>>
>>>>> {TARGETENTRY
>>>>> :resdom
>>>>> {RESDOM
>>>>> :resno 5
>>>>> :restype 23
>>>>> :restypmod -1
>>>>> :resname filenameid
>>>>> :ressortgroupref 0
>>>>> :resorigtbl 440806231
>>>>> :resorigcol 5
>>>>> :resjunk false
>>>>> }
>>>>>
>>>>> :expr
>>>>> {VAR
>>>>> :varno 1
>>>>> :varattno 5
>>>>> :vartype 23
>>>>> :vartypmod -1
>>>>> :varlevelsup 0
>>>>> :varnoold 1
>>>>> :varoattno 5
>>>>> }
>>>>> }
>>>>>
>>>>> {TARGETENTRY
>>>>> :resdom
>>>>> {RESDOM
>>>>> :resno 6
>>>>> :restype 23
>>>>> :restypmod -1
>>>>> :resname markid
>>>>> :ressortgroupref 0
>>>>> :resorigtbl 440806231
>>>>> :resorigcol 6
>>>>> :resjunk false
>>>>> }
>>>>>
>>>>> :expr
>>>>> {VAR
>>>>> :varno 1
>>>>> :varattno 6
>>>>> :vartype 23
>>>>> :vartypmod -1
>>>>> :varlevelsup 0
>>>>> :varnoold 1
>>>>> :varoattno 6
>>>>> }
>>>>> }
>>>>>
>>>>> {TARGETENTRY
>>>>> :resdom
>>>>> {RESDOM
>>>>> :resno 7
>>>>> :restype 25
>>>>> :restypmod -1
>>>>> :resname lstat
>>>>> :ressortgroupref 0
>>>>> :resorigtbl 440806231
>>>>> :resorigcol 7
>>>>> :resjunk false
>>>>> }
>>>>>
>>>>> :expr
>>>>> {VAR
>>>>> :varno 1
>>>>> :varattno 7
>>>>> :vartype 25
>>>>> :vartypmod -1
>>>>> :varlevelsup 0
>>>>> :varnoold 1
>>>>> :varoattno 7
>>>>> }
>>>>> }
>>>>>
>>>>> {TARGETENTRY
>>>>> :resdom
>>>>> {RESDOM
>>>>> :resno 8
>>>>> :restype 25
>>>>> :restypmod -1
>>>>> :resname md5
>>>>> :ressortgroupref 0
>>>>> :resorigtbl 440806231
>>>>> :resorigcol 8
>>>>> :resjunk false
>>>>> }
>>>>>
>>>>> :expr
>>>>> {VAR
>>>>> :varno 1
>>>>> :varattno 8
>>>>> :vartype 25
>>>>> :vartypmod -1
>>>>> :varlevelsup 0
>>>>> :varnoold 1
>>>>> :varoattno 8
>>>>> }
>>>>> }
>>>>> )
>>>>>
>>>>> :qual (
>>>>> {OPEXPR
>>>>> :opno 96
>>>>> :opfuncid 65
>>>>> :opresulttype 16
>>>>> :opretset false
>>>>> :args (
>>>>> {VAR
>>>>> :varno 1
>>>>> :varattno 3
>>>>> :vartype 23
>>>>> :vartypmod -1
>>>>> :varlevelsup 0
>>>>> :varnoold 1
>>>>> :varoattno 3
>>>>> }
>>>>>
>>>>> {CONST
>>>>> :consttype 23
>>>>> :constlen 4
>>>>> :constbyval true
>>>>> :constisnull false
>>>>> :constvalue 4 [ -24 10 0 0 ]
>>>>> }
>>>>> )
>>>>> }
>>>>> )
>>>>>
>>>>> :lefttree <>
>>>>> :righttree <>
>>>>> :initPlan <>
>>>>> :extParam ()
>>>>>
>>>>> :allParam ()
>>>>>
>>>>> :nParamExec 0
>>>>> :scanrelid 1
>>>>> }
>>>>>
>>>>> Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual
>>>>> time=103215.145..161153.664 rows=1 loops=1)
>>>>> Filter: (jobid = 2792)
>>>>> Total runtime: 161154.734 ms
>>>>>
>>>>>
>>>>> --
>>>>> Best regards,
>>>>>
>>>>> Marc
>>>>>
>>>>> --
>>>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>>>> To make changes to your subscription:
>>>>> http://www.postgresql.org/mailpref/pgsql-general
>>>>>
>>
>

--
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 not using an index

Hi Pavel,

Pavel Stehule schreef:
> 2008/8/1 Marc Cuypers <m.cuypers@mgvd.be>:
>> Hi Pavel,
>>
>> Isn't the text for the Verbose Explain analyze not enough?
>> Is not, how can i generate it?
>>
>> --
>
>
> no, I am missing statistics info
>
> try
> EXPLAIN ANALYZE SELECT .....
>
> regards

I entered the command in pgsql and got the following output:

# explain analyze select * from file where jobid=2792;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual
time=37738.780..90453.299 rows=1 loops=1)
Filter: (jobid = 2792)
Total runtime: 90453.419 ms
(3 rows)

Makes any sence?

--
Marc

>>
>> Marc
>>
>> Pavel Stehule schreef:
>>> Hello
>>>
>>> please, send EXPLAIN ANALYZE output.
>>>
>>> regards
>>> Pavel Stehule
>>>
>>> 2008/8/1 Marc Cuypers <m.cuypers@mgvd.be>:
>>>> Hi,
>>>>
>>>> I'm using postgres 7.4 and bacula 1.38 on debian.
>>>>
>>>> In the bacula database there is a table named 'file' which has about 2.5
>>>> million rows.
>>>> In this table there is a field 'jobid' which is indexed.
>>>> The index is created with the following command:
>>>> CREATE INDEX file_jobid_idx ON file USING btree (jobid);
>>>>
>>>> The query:
>>>> SELECT * from file where jobid=2792
>>>>
>>>> does a full scan and to my opinion it doesn't use the index.
>>>> I already did a VACUUM ANALYZE on the database.
>>>>
>>>>
>>>> Somebody an idea?
>>>>
>>>> EXPLAIN tells the following:
>>>> Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual
>>>> time=103215.145..161153.664 rows=1 loops=1)
>>>> Filter: (jobid = 2792)
>>>> Total runtime: 161154.734 ms
>>>>
>>>> The Verbose Explain tells the following:
>>>> {SEQSCAN
>>>> :startup_cost 0.00
>>>> :total_cost 707683.30
>>>> :plan_rows 207562
>>>> :plan_width 110
>>>> :targetlist (
>>>> {TARGETENTRY
>>>> :resdom
>>>> {RESDOM
>>>> :resno 1
>>>> :restype 23
>>>> :restypmod -1
>>>> :resname fileid
>>>> :ressortgroupref 0
>>>> :resorigtbl 440806231
>>>> :resorigcol 1
>>>> :resjunk false
>>>> }
>>>>
>>>> :expr
>>>> {VAR
>>>> :varno 1
>>>> :varattno 1
>>>> :vartype 23
>>>> :vartypmod -1
>>>> :varlevelsup 0
>>>> :varnoold 1
>>>> :varoattno 1
>>>> }
>>>> }
>>>>
>>>> {TARGETENTRY
>>>> :resdom
>>>> {RESDOM
>>>> :resno 2
>>>> :restype 23
>>>> :restypmod -1
>>>> :resname fileindex
>>>> :ressortgroupref 0
>>>> :resorigtbl 440806231
>>>> :resorigcol 2
>>>> :resjunk false
>>>> }
>>>>
>>>> :expr
>>>> {VAR
>>>> :varno 1
>>>> :varattno 2
>>>> :vartype 23
>>>> :vartypmod -1
>>>> :varlevelsup 0
>>>> :varnoold 1
>>>> :varoattno 2
>>>> }
>>>> }
>>>>
>>>> {TARGETENTRY
>>>> :resdom
>>>> {RESDOM
>>>> :resno 3
>>>> :restype 23
>>>> :restypmod -1
>>>> :resname jobid
>>>> :ressortgroupref 0
>>>> :resorigtbl 440806231
>>>> :resorigcol 3
>>>> :resjunk false
>>>> }
>>>>
>>>> :expr
>>>> {VAR
>>>> :varno 1
>>>> :varattno 3
>>>> :vartype 23
>>>> :vartypmod -1
>>>> :varlevelsup 0
>>>> :varnoold 1
>>>> :varoattno 3
>>>> }
>>>> }
>>>>
>>>> {TARGETENTRY
>>>> :resdom
>>>> {RESDOM
>>>> :resno 4
>>>> :restype 23
>>>> :restypmod -1
>>>> :resname pathid
>>>> :ressortgroupref 0
>>>> :resorigtbl 440806231
>>>> :resorigcol 4
>>>> :resjunk false
>>>> }
>>>>
>>>> :expr
>>>> {VAR
>>>> :varno 1
>>>> :varattno 4
>>>> :vartype 23
>>>> :vartypmod -1
>>>> :varlevelsup 0
>>>> :varnoold 1
>>>> :varoattno 4
>>>> }
>>>> }
>>>>
>>>> {TARGETENTRY
>>>> :resdom
>>>> {RESDOM
>>>> :resno 5
>>>> :restype 23
>>>> :restypmod -1
>>>> :resname filenameid
>>>> :ressortgroupref 0
>>>> :resorigtbl 440806231
>>>> :resorigcol 5
>>>> :resjunk false
>>>> }
>>>>
>>>> :expr
>>>> {VAR
>>>> :varno 1
>>>> :varattno 5
>>>> :vartype 23
>>>> :vartypmod -1
>>>> :varlevelsup 0
>>>> :varnoold 1
>>>> :varoattno 5
>>>> }
>>>> }
>>>>
>>>> {TARGETENTRY
>>>> :resdom
>>>> {RESDOM
>>>> :resno 6
>>>> :restype 23
>>>> :restypmod -1
>>>> :resname markid
>>>> :ressortgroupref 0
>>>> :resorigtbl 440806231
>>>> :resorigcol 6
>>>> :resjunk false
>>>> }
>>>>
>>>> :expr
>>>> {VAR
>>>> :varno 1
>>>> :varattno 6
>>>> :vartype 23
>>>> :vartypmod -1
>>>> :varlevelsup 0
>>>> :varnoold 1
>>>> :varoattno 6
>>>> }
>>>> }
>>>>
>>>> {TARGETENTRY
>>>> :resdom
>>>> {RESDOM
>>>> :resno 7
>>>> :restype 25
>>>> :restypmod -1
>>>> :resname lstat
>>>> :ressortgroupref 0
>>>> :resorigtbl 440806231
>>>> :resorigcol 7
>>>> :resjunk false
>>>> }
>>>>
>>>> :expr
>>>> {VAR
>>>> :varno 1
>>>> :varattno 7
>>>> :vartype 25
>>>> :vartypmod -1
>>>> :varlevelsup 0
>>>> :varnoold 1
>>>> :varoattno 7
>>>> }
>>>> }
>>>>
>>>> {TARGETENTRY
>>>> :resdom
>>>> {RESDOM
>>>> :resno 8
>>>> :restype 25
>>>> :restypmod -1
>>>> :resname md5
>>>> :ressortgroupref 0
>>>> :resorigtbl 440806231
>>>> :resorigcol 8
>>>> :resjunk false
>>>> }
>>>>
>>>> :expr
>>>> {VAR
>>>> :varno 1
>>>> :varattno 8
>>>> :vartype 25
>>>> :vartypmod -1
>>>> :varlevelsup 0
>>>> :varnoold 1
>>>> :varoattno 8
>>>> }
>>>> }
>>>> )
>>>>
>>>> :qual (
>>>> {OPEXPR
>>>> :opno 96
>>>> :opfuncid 65
>>>> :opresulttype 16
>>>> :opretset false
>>>> :args (
>>>> {VAR
>>>> :varno 1
>>>> :varattno 3
>>>> :vartype 23
>>>> :vartypmod -1
>>>> :varlevelsup 0
>>>> :varnoold 1
>>>> :varoattno 3
>>>> }
>>>>
>>>> {CONST
>>>> :consttype 23
>>>> :constlen 4
>>>> :constbyval true
>>>> :constisnull false
>>>> :constvalue 4 [ -24 10 0 0 ]
>>>> }
>>>> )
>>>> }
>>>> )
>>>>
>>>> :lefttree <>
>>>> :righttree <>
>>>> :initPlan <>
>>>> :extParam ()
>>>>
>>>> :allParam ()
>>>>
>>>> :nParamExec 0
>>>> :scanrelid 1
>>>> }
>>>>
>>>> Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual
>>>> time=103215.145..161153.664 rows=1 loops=1)
>>>> Filter: (jobid = 2792)
>>>> Total runtime: 161154.734 ms
>>>>
>>>>
>>>> --
>>>> Best regards,
>>>>
>>>> Marc
>>>>
>>>> --
>>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-general
>>>>
>

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

[COMMITTERS] pgsql: Rearrange the code in auth.c so that all functions for a single

Log Message:
-----------
Rearrange the code in auth.c so that all functions for a single authentication
method is grouped together in a reasonably similar way, keeping the "global
shared functions" together in their own section as well. Makes it a lot easier
to find your way around the code.

Modified Files:
--------------
pgsql/src/backend/libpq:
auth.c (r1.166 -> r1.167)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/libpq/auth.c?r1=1.166&r2=1.167)

--
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] Relation forks & FSM rewrite patches

Attached is an new version of the relation forks and FSM patches,
updated per Tom's comments (details below). I think the relation forks
patch is ready for commit, except that the docs on physical storage
needs to be updated. Barring any objections, I will commit the relation
forks patch in a few days, and submit a patch for the docs.

The FSM patch has been updated so that it applied over the new relation
forks patch.

Tom Lane wrote:
> "Heikki Linnakangas" <heikki@enterprisedb.com> writes:
>> The one part that I'm not totally satisfied in the relation forks patch
>> is the smgrcreate() function. The question problem is: which piece of
>> code decides which forks to create for a relation, and when to create
>> them? I settled on the concept that all forks that a relation will need
>> are created at once, in one smgrcreate() call. There's no function to
>> create additional forks later on.
>
> I think that's an extremely bad idea. You need look no further than
> Zdenek's hopes of in-place-upgrade to see a need for adding a fork
> to an existing relation; but even without that I can see possibly
> wanting to not create a fork right away. I think smgrcreate() should
> just create one fork as it's told (ie, same API you gave mdcreate).

Yeah, that's better.

> Other nits:
>
> relpath() is now in danger of buffer overrun: you need to increase
> the palloc() sizes.

Oops, fixed.

> Seems like a #define for the max number of digits
> in a ForkNumber wouldn't be out of place (though I sure hope it never
> gets past 1 ...).

Done. It makes it more obvious how the buffer length is calculated than
using plain numbers.

> Also, I strongly suggest *not* appending _0 to the name of the main fork's
> file. This'd break on-disk compatibility and people's expectations,
> for no particular reason.

Done.

> Don't like the name NUM_FORKS; it seems to suggest that's the actual
> number of forks in existence. MAX_NUM_FORKS would be better.

I don't like MAX_NUM_FORKS. I renamed it to MAX_FORKNUM (and changed its
semantics accordingly).

> I think that setting InvalidForkNumber to -1 is unportable: there is
> nothing compelling the compiler to represent enum ForkNumber as a signed
> type, so the places where you assume a ForkNumber variable can hold
> InvalidForkNumber all look like trouble. One solution is to include
> InvalidForkNumber in the enum:
>
> enum ForkNumber {
> InvalidForkNumber = -1,
> MAIN_FORKNUM = 0
> };
>
> This would be a bit annoying if someone wrote a switch statement listing
> different possible fork numbers, as the compiler would complain if
> there's no case for InvalidForkNumber; but I can't see a reason for code
> like that to be common.

I chose this approach. There's no switch constructs like that at the
moment, and I don't see any immediate need for one. In fact, at the
moment InvalidForkNumber is only used in bgwriter database fsync
requests, where the fork number field doesn't mean anything.

> BTW, don't put a comma at end of enum ForkNumber, I think some compilers
> will barf on that.

Done.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Re: [GENERAL] eliminating records not in (select id ... so SLOW?

Ivan Sergio Borgonovo wrote:

> But what if I *really* had to execute that query?
> Any other magic I could play to speed it up?

A trick that is sometimes spectacularly efficient is to rewrite the
query to use an outer join instead of NOT IN.

Try:

DELETE FROM table1 WHERE id IN
(SELECT table1.id FROM table1 LEFT JOIN table2 ON table1.id=table2.id
WHERE table2.id IS NULL)

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

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

[SQL] regexp_replace

Hi all. I'd like to know whether it's possible to reverse the
behaviour of regexp_replace, meaning :
now if I do
SELECT regexp_replace ('foobarbaz', 'b..', 'X') I get 'fooXbaz' - it
replaces the string that matches given pattern with 'X', how do I
achieve the opposite - replace the string that doesn't match the
pattern ?

regards
mk

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

Re: [PERFORM] Database size Vs performance degradation

On Thu, 31 Jul 2008, Andrzej Zawadzki wrote:
> Maybe I'm wrong but if this "bulk insert and delete" process is cyclical then
> You don't need vacuum full.
> Released tuples will fill up again with fresh data next day - after regular
> vacuum.

Yes, a regular manual vacuum will prevent the table from growing more than
it needs to. However, a vacuum full is required to actually reduce the
size of the table from 7.5G to 2.7G if that hasn't been done on the
production system already.

Matthew

--
It's one of those irregular verbs - "I have an independent mind," "You are
an eccentric," "He is round the twist."
-- Bernard Woolly, Yes Prime Minister

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

[GENERAL] function definition and "entity"

I've to refactor a bunch of functions.
Some of them are called inside other functions.
Most of them could be changed through create or replace so...
calling function should still refer to the right newer function.

But some had to be dropped because the change was in output param.

Is there a way to automatically check if all references are to the
correct existing function?

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

--
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 not using an index

2008/8/1 Marc Cuypers <m.cuypers@mgvd.be>:
> Hi Pavel,
>
> Isn't the text for the Verbose Explain analyze not enough?
> Is not, how can i generate it?
>
> --

no, I am missing statistics info

try
EXPLAIN ANALYZE SELECT .....

regards
Pavel Stehule
> Best regards,
>
> Marc
>
> Pavel Stehule schreef:
>>
>> Hello
>>
>> please, send EXPLAIN ANALYZE output.
>>
>> regards
>> Pavel Stehule
>>
>> 2008/8/1 Marc Cuypers <m.cuypers@mgvd.be>:
>>>
>>> Hi,
>>>
>>> I'm using postgres 7.4 and bacula 1.38 on debian.
>>>
>>> In the bacula database there is a table named 'file' which has about 2.5
>>> million rows.
>>> In this table there is a field 'jobid' which is indexed.
>>> The index is created with the following command:
>>> CREATE INDEX file_jobid_idx ON file USING btree (jobid);
>>>
>>> The query:
>>> SELECT * from file where jobid=2792
>>>
>>> does a full scan and to my opinion it doesn't use the index.
>>> I already did a VACUUM ANALYZE on the database.
>>>
>>>
>>> Somebody an idea?
>>>
>>> EXPLAIN tells the following:
>>> Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual
>>> time=103215.145..161153.664 rows=1 loops=1)
>>> Filter: (jobid = 2792)
>>> Total runtime: 161154.734 ms
>>>
>>> The Verbose Explain tells the following:
>>> {SEQSCAN
>>> :startup_cost 0.00
>>> :total_cost 707683.30
>>> :plan_rows 207562
>>> :plan_width 110
>>> :targetlist (
>>> {TARGETENTRY
>>> :resdom
>>> {RESDOM
>>> :resno 1
>>> :restype 23
>>> :restypmod -1
>>> :resname fileid
>>> :ressortgroupref 0
>>> :resorigtbl 440806231
>>> :resorigcol 1
>>> :resjunk false
>>> }
>>>
>>> :expr
>>> {VAR
>>> :varno 1
>>> :varattno 1
>>> :vartype 23
>>> :vartypmod -1
>>> :varlevelsup 0
>>> :varnoold 1
>>> :varoattno 1
>>> }
>>> }
>>>
>>> {TARGETENTRY
>>> :resdom
>>> {RESDOM
>>> :resno 2
>>> :restype 23
>>> :restypmod -1
>>> :resname fileindex
>>> :ressortgroupref 0
>>> :resorigtbl 440806231
>>> :resorigcol 2
>>> :resjunk false
>>> }
>>>
>>> :expr
>>> {VAR
>>> :varno 1
>>> :varattno 2
>>> :vartype 23
>>> :vartypmod -1
>>> :varlevelsup 0
>>> :varnoold 1
>>> :varoattno 2
>>> }
>>> }
>>>
>>> {TARGETENTRY
>>> :resdom
>>> {RESDOM
>>> :resno 3
>>> :restype 23
>>> :restypmod -1
>>> :resname jobid
>>> :ressortgroupref 0
>>> :resorigtbl 440806231
>>> :resorigcol 3
>>> :resjunk false
>>> }
>>>
>>> :expr
>>> {VAR
>>> :varno 1
>>> :varattno 3
>>> :vartype 23
>>> :vartypmod -1
>>> :varlevelsup 0
>>> :varnoold 1
>>> :varoattno 3
>>> }
>>> }
>>>
>>> {TARGETENTRY
>>> :resdom
>>> {RESDOM
>>> :resno 4
>>> :restype 23
>>> :restypmod -1
>>> :resname pathid
>>> :ressortgroupref 0
>>> :resorigtbl 440806231
>>> :resorigcol 4
>>> :resjunk false
>>> }
>>>
>>> :expr
>>> {VAR
>>> :varno 1
>>> :varattno 4
>>> :vartype 23
>>> :vartypmod -1
>>> :varlevelsup 0
>>> :varnoold 1
>>> :varoattno 4
>>> }
>>> }
>>>
>>> {TARGETENTRY
>>> :resdom
>>> {RESDOM
>>> :resno 5
>>> :restype 23
>>> :restypmod -1
>>> :resname filenameid
>>> :ressortgroupref 0
>>> :resorigtbl 440806231
>>> :resorigcol 5
>>> :resjunk false
>>> }
>>>
>>> :expr
>>> {VAR
>>> :varno 1
>>> :varattno 5
>>> :vartype 23
>>> :vartypmod -1
>>> :varlevelsup 0
>>> :varnoold 1
>>> :varoattno 5
>>> }
>>> }
>>>
>>> {TARGETENTRY
>>> :resdom
>>> {RESDOM
>>> :resno 6
>>> :restype 23
>>> :restypmod -1
>>> :resname markid
>>> :ressortgroupref 0
>>> :resorigtbl 440806231
>>> :resorigcol 6
>>> :resjunk false
>>> }
>>>
>>> :expr
>>> {VAR
>>> :varno 1
>>> :varattno 6
>>> :vartype 23
>>> :vartypmod -1
>>> :varlevelsup 0
>>> :varnoold 1
>>> :varoattno 6
>>> }
>>> }
>>>
>>> {TARGETENTRY
>>> :resdom
>>> {RESDOM
>>> :resno 7
>>> :restype 25
>>> :restypmod -1
>>> :resname lstat
>>> :ressortgroupref 0
>>> :resorigtbl 440806231
>>> :resorigcol 7
>>> :resjunk false
>>> }
>>>
>>> :expr
>>> {VAR
>>> :varno 1
>>> :varattno 7
>>> :vartype 25
>>> :vartypmod -1
>>> :varlevelsup 0
>>> :varnoold 1
>>> :varoattno 7
>>> }
>>> }
>>>
>>> {TARGETENTRY
>>> :resdom
>>> {RESDOM
>>> :resno 8
>>> :restype 25
>>> :restypmod -1
>>> :resname md5
>>> :ressortgroupref 0
>>> :resorigtbl 440806231
>>> :resorigcol 8
>>> :resjunk false
>>> }
>>>
>>> :expr
>>> {VAR
>>> :varno 1
>>> :varattno 8
>>> :vartype 25
>>> :vartypmod -1
>>> :varlevelsup 0
>>> :varnoold 1
>>> :varoattno 8
>>> }
>>> }
>>> )
>>>
>>> :qual (
>>> {OPEXPR
>>> :opno 96
>>> :opfuncid 65
>>> :opresulttype 16
>>> :opretset false
>>> :args (
>>> {VAR
>>> :varno 1
>>> :varattno 3
>>> :vartype 23
>>> :vartypmod -1
>>> :varlevelsup 0
>>> :varnoold 1
>>> :varoattno 3
>>> }
>>>
>>> {CONST
>>> :consttype 23
>>> :constlen 4
>>> :constbyval true
>>> :constisnull false
>>> :constvalue 4 [ -24 10 0 0 ]
>>> }
>>> )
>>> }
>>> )
>>>
>>> :lefttree <>
>>> :righttree <>
>>> :initPlan <>
>>> :extParam ()
>>>
>>> :allParam ()
>>>
>>> :nParamExec 0
>>> :scanrelid 1
>>> }
>>>
>>> Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual
>>> time=103215.145..161153.664 rows=1 loops=1)
>>> Filter: (jobid = 2792)
>>> Total runtime: 161154.734 ms
>>>
>>>
>>> --
>>> Best regards,
>>>
>>> Marc
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>

--
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 change a view's owner in postgres

On Fri, Aug 1, 2008 at 11:41 AM, Anoop G <anoopmadavoor@gmail.com> wrote:
> How to change a view's owner in postgres?

ALTER TABLE view_name OWNER TO new_owner;

--
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 change a view's owner in postgres(is it possible?)

Dear all,

We can change the owner of a tbale like this

alter table tbl_year_end owner to anoop;

Is it possible to change the owner name of a  view through sql?

[COMMITTERS] pgbouncer - pgbouncer: change ver to 1.2.1

Log Message:
-----------
change ver to 1.2.1

Modified Files:
--------------
pgbouncer:
NEWS (r1.23 -> r1.24)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/NEWS.diff?r1=1.23&r2=1.24)
pgbouncer/debian:
changelog (r1.13 -> r1.14)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/debian/changelog.diff?r1=1.13&r2=1.14)

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

[COMMITTERS] pgbouncer - pgbouncer: new drop_on_error param

Log Message:
-----------
new drop_on_error param

Modified Files:
--------------
pgbouncer/doc:
config.txt (r1.10 -> r1.11)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/doc/config.txt.diff?r1=1.10&r2=1.11)
todo.txt (r1.28 -> r1.29)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/doc/todo.txt.diff?r1=1.28&r2=1.29)
pgbouncer/include:
bouncer.h (r1.17 -> r1.18)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/include/bouncer.h.diff?r1=1.17&r2=1.18)
pgbouncer/src:
main.c (r1.43 -> r1.44)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/src/main.c.diff?r1=1.43&r2=1.44)
objects.c (r1.47 -> r1.48)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/src/objects.c.diff?r1=1.47&r2=1.48)
server.c (r1.28 -> r1.29)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgbouncer/pgbouncer/src/server.c.diff?r1=1.28&r2=1.29)

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

Re: [GENERAL] Postgresql not using an index

Hi Pavel,

Isn't the text for the Verbose Explain analyze not enough?
Is not, how can i generate it?

--
Best regards,

Marc

Pavel Stehule schreef:
> Hello
>
> please, send EXPLAIN ANALYZE output.
>
> regards
> Pavel Stehule
>
> 2008/8/1 Marc Cuypers <m.cuypers@mgvd.be>:
>> Hi,
>>
>> I'm using postgres 7.4 and bacula 1.38 on debian.
>>
>> In the bacula database there is a table named 'file' which has about 2.5
>> million rows.
>> In this table there is a field 'jobid' which is indexed.
>> The index is created with the following command:
>> CREATE INDEX file_jobid_idx ON file USING btree (jobid);
>>
>> The query:
>> SELECT * from file where jobid=2792
>>
>> does a full scan and to my opinion it doesn't use the index.
>> I already did a VACUUM ANALYZE on the database.
>>
>>
>> Somebody an idea?
>>
>> EXPLAIN tells the following:
>> Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual
>> time=103215.145..161153.664 rows=1 loops=1)
>> Filter: (jobid = 2792)
>> Total runtime: 161154.734 ms
>>
>> The Verbose Explain tells the following:
>> {SEQSCAN
>> :startup_cost 0.00
>> :total_cost 707683.30
>> :plan_rows 207562
>> :plan_width 110
>> :targetlist (
>> {TARGETENTRY
>> :resdom
>> {RESDOM
>> :resno 1
>> :restype 23
>> :restypmod -1
>> :resname fileid
>> :ressortgroupref 0
>> :resorigtbl 440806231
>> :resorigcol 1
>> :resjunk false
>> }
>>
>> :expr
>> {VAR
>> :varno 1
>> :varattno 1
>> :vartype 23
>> :vartypmod -1
>> :varlevelsup 0
>> :varnoold 1
>> :varoattno 1
>> }
>> }
>>
>> {TARGETENTRY
>> :resdom
>> {RESDOM
>> :resno 2
>> :restype 23
>> :restypmod -1
>> :resname fileindex
>> :ressortgroupref 0
>> :resorigtbl 440806231
>> :resorigcol 2
>> :resjunk false
>> }
>>
>> :expr
>> {VAR
>> :varno 1
>> :varattno 2
>> :vartype 23
>> :vartypmod -1
>> :varlevelsup 0
>> :varnoold 1
>> :varoattno 2
>> }
>> }
>>
>> {TARGETENTRY
>> :resdom
>> {RESDOM
>> :resno 3
>> :restype 23
>> :restypmod -1
>> :resname jobid
>> :ressortgroupref 0
>> :resorigtbl 440806231
>> :resorigcol 3
>> :resjunk false
>> }
>>
>> :expr
>> {VAR
>> :varno 1
>> :varattno 3
>> :vartype 23
>> :vartypmod -1
>> :varlevelsup 0
>> :varnoold 1
>> :varoattno 3
>> }
>> }
>>
>> {TARGETENTRY
>> :resdom
>> {RESDOM
>> :resno 4
>> :restype 23
>> :restypmod -1
>> :resname pathid
>> :ressortgroupref 0
>> :resorigtbl 440806231
>> :resorigcol 4
>> :resjunk false
>> }
>>
>> :expr
>> {VAR
>> :varno 1
>> :varattno 4
>> :vartype 23
>> :vartypmod -1
>> :varlevelsup 0
>> :varnoold 1
>> :varoattno 4
>> }
>> }
>>
>> {TARGETENTRY
>> :resdom
>> {RESDOM
>> :resno 5
>> :restype 23
>> :restypmod -1
>> :resname filenameid
>> :ressortgroupref 0
>> :resorigtbl 440806231
>> :resorigcol 5
>> :resjunk false
>> }
>>
>> :expr
>> {VAR
>> :varno 1
>> :varattno 5
>> :vartype 23
>> :vartypmod -1
>> :varlevelsup 0
>> :varnoold 1
>> :varoattno 5
>> }
>> }
>>
>> {TARGETENTRY
>> :resdom
>> {RESDOM
>> :resno 6
>> :restype 23
>> :restypmod -1
>> :resname markid
>> :ressortgroupref 0
>> :resorigtbl 440806231
>> :resorigcol 6
>> :resjunk false
>> }
>>
>> :expr
>> {VAR
>> :varno 1
>> :varattno 6
>> :vartype 23
>> :vartypmod -1
>> :varlevelsup 0
>> :varnoold 1
>> :varoattno 6
>> }
>> }
>>
>> {TARGETENTRY
>> :resdom
>> {RESDOM
>> :resno 7
>> :restype 25
>> :restypmod -1
>> :resname lstat
>> :ressortgroupref 0
>> :resorigtbl 440806231
>> :resorigcol 7
>> :resjunk false
>> }
>>
>> :expr
>> {VAR
>> :varno 1
>> :varattno 7
>> :vartype 25
>> :vartypmod -1
>> :varlevelsup 0
>> :varnoold 1
>> :varoattno 7
>> }
>> }
>>
>> {TARGETENTRY
>> :resdom
>> {RESDOM
>> :resno 8
>> :restype 25
>> :restypmod -1
>> :resname md5
>> :ressortgroupref 0
>> :resorigtbl 440806231
>> :resorigcol 8
>> :resjunk false
>> }
>>
>> :expr
>> {VAR
>> :varno 1
>> :varattno 8
>> :vartype 25
>> :vartypmod -1
>> :varlevelsup 0
>> :varnoold 1
>> :varoattno 8
>> }
>> }
>> )
>>
>> :qual (
>> {OPEXPR
>> :opno 96
>> :opfuncid 65
>> :opresulttype 16
>> :opretset false
>> :args (
>> {VAR
>> :varno 1
>> :varattno 3
>> :vartype 23
>> :vartypmod -1
>> :varlevelsup 0
>> :varnoold 1
>> :varoattno 3
>> }
>>
>> {CONST
>> :consttype 23
>> :constlen 4
>> :constbyval true
>> :constisnull false
>> :constvalue 4 [ -24 10 0 0 ]
>> }
>> )
>> }
>> )
>>
>> :lefttree <>
>> :righttree <>
>> :initPlan <>
>> :extParam ()
>>
>> :allParam ()
>>
>> :nParamExec 0
>> :scanrelid 1
>> }
>>
>> Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual
>> time=103215.145..161153.664 rows=1 loops=1)
>> Filter: (jobid = 2792)
>> Total runtime: 161154.734 ms
>>
>>
>> --
>> Best regards,
>>
>> Marc
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>

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

Re: [pgadmin-hackers] Support for integrated tsearch configuration

Dave Page a écrit :
> On Sat, Jun 28, 2008 at 1:54 PM, Guillaume Lelarge
> [...]
>> I was sure I was following the "collection nodes and objects nodes" way of
>> doing. Here are some ascii artwork:
>>
>> - Databases (3)
>> + DB1
>> + DB2
>> - DB3
>> + Catalogs (2)
>> + Casts (0)
>> + Languages (0)
>> + Schemas (1)
>> + Replication (0)
>> - Text Search
>> + Configurations (2)
>> + Dictionaries (2)
>> + Parsers (2)
>> + Templates (2)
>>
>> Seems you're right. The Text search node is neither a collection neither an
>> object one. Unfortunately, I don't have a better one.
>
> Logically I think the design is correct - the only 'more correct' way
> would be to put the objects directly under a database, but that would
> be quite ugly I think.
>
> What you propose should work - it just might need a little thought.
> Maybe we need a 'pgGroupObject' node type. Something to ponder on in
> spare minutes rather than your 5 days I suspect :-)
>

I worked three days on it. The fourth was more about testing it on
different platforms (GTK, Windows, Mac). Now, It's working. I don't
attach the patch because it's really big, but here is a URL to get it
compressed:

http://developer.pgadmin.org/~guillaume/fts.patch.bz2

I was wrong on the layout of the text search objects inside the
treeview. They all belong to a schema, so they are part of a schema
node. You can see some screenshots on my blog:

* the treeview:
http://blog.guillaume.lelarge.info/public/pgadmin_fts/fts5.png

* text search configuration dialog:
http://blog.guillaume.lelarge.info/public/pgadmin_fts/fts3.png
(not uptodate screenshot, it lacks the Tokens tab)
http://blog.guillaume.lelarge.info/public/pgadmin_fts/fts9.png

* text search dictionary dialog:
http://blog.guillaume.lelarge.info/public/pgadmin_fts/fts7.png
http://blog.guillaume.lelarge.info/public/pgadmin_fts/fts8.png
(not uptodate screenshot, the add and changes buttons merge in a
single one)

* text search parser dialog:
http://blog.guillaume.lelarge.info/public/pgadmin_fts/fts6.png

* text search template dialog:
http://blog.guillaume.lelarge.info/public/pgadmin_fts/fts4.png

They all are resizable, so the ones that use wxListCtrl widget suffer
the Mac issue.

Comments?

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

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

Re: [pgsql-es-ayuda] sobre savepoints

--------------------------------------------------
From: "Alvaro Herrera" <alvherre@alvh.no-ip.org>
Sent: Thursday, July 31, 2008 9:19 PM
To: "Linos" <info@linos.es>
Cc: "Lista PostgreSql" <pgsql-es-ayuda@postgresql.org>
Subject: Re: [pgsql-es-ayuda] sobre savepoints

> Linos escribió:
>> tambien podria dentro de la funcion quizas lanzar primero el select y
>> segun lanzar un insert o un update, no? deberia ser mas rapido que el
>> savepoint? lo que no se como resolver en plpsql es el tema del numero de
>> columnas variable pero supongo q habra alguna forma, no?
>
> El problema es que para hacer un select para verificar si necesitas
> update o insert, necesitarias bloquear la tabla de antemano; de lo
> contrario puede pasar que hagas el select, diga que no esta el registro,
> y justo algun otro proceso lo inserte antes que tu alcances a
> insertarlo.
>
> Si no te complica bloquear la tabla, entonces creo que este
> procedimiento seria lo mas rapido. (Digo "creo" porque es posible que
> la otra alternativa es hacerlo con un "upsert" usando un savepoint. Hay
> un procedimiento de ejemplo de esto en la documentacion de Postgres. La
> gracia del upsert es que solo tienes que hacer un recorrido de la tabla
> en el caso que funcione a la primera; en cambio si bloqueas la tabla
> tienes que hacer primero el select y despues el insert o update, o sea
> son dos recorridos en todos los casos. Sin embargo tiene la desventaja
> de tener que crear y destruir el savepoint por cada registro).
>

Alvaro ... el UPSERT lo veo dentro de los TODO de postgresql .... o estoy
equivocado?

Atentamente,

RAUL DUQUE
Bogotá, Colombia

> --
> Alvaro Herrera
> http://www.PlanetPostgreSQL.org/
> "After a quick R of TFM, all I can say is HOLY CR** THAT IS COOL!
> PostgreSQL was
> amazing when I first started using it at 7.2, and I'm continually
> astounded by
> learning new features and techniques made available by the continuing work
> of
> the development team."
> Berend Tober,
> http://archives.postgresql.org/pgsql-hackers/2007-08/msg01009.php
> --
> TIP 4: No hagas 'kill -9' a postmaster
>
> __________ Information from ESET Smart Security, version of virus
> signature database 3316 (20080731) __________
>
> The message was checked by ESET Smart Security.
>
> http://www.eset.com
>
>
--
TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net

Re: [GENERAL] Postgresql not using an index

Marc --

> Hi,
>
> I'm using postgres 7.4 and bacula 1.38 on debian.
>
> In the bacula database there is a table named 'file' which has about 2.5
> million rows.
> In this table there is a field 'jobid' which is indexed.
> The index is created with the following command:
>       CREATE INDEX file_jobid_idx  ON file  USING btree  (jobid);
>
> The query:
>       SELECT * from file where jobid=2792
>
> does a full scan and to my opinion it doesn't use the index.
> I already did a VACUUM ANALYZE on the database.
>
> Somebody an idea?
>
> EXPLAIN tells the following:
> Seq Scan on file  (cost=0.00..707683.30 rows=207562 width=110) (actual
> time=103215.145..161153.664 rows=1 loops=1)
>    Filter: (jobid = 2792)
> Total runtime: 161154.734 ms

Perhaps jobid is not an int -- maybe a bigint ? In which case the types don't match and the index won't be used (newer versions might do ok but 7.4 won't IIRC).

Perhaps we could see the table description from \d in the psql tool ?

HTH,

Greg Williamson
Senior DBA
DigitalGlobe

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)

Re: [pgadmin-hackers] Minimum height of server status dialog

Magnus Hagander a écrit :
> Guillaume Lelarge wrote:
>>> The hotkey stuff I was working on already, but I'll just use your patch
>>> instead. It seems to be working fine in on my machine as well, so I'll
>>> go ahead and commit it. One thing first though...
>>>
>>> When I ran embed-xrc, I got a diff containing:
>>> Index: ui/xrcDialogs.cpp
>>> ===================================================================
>>> --- ui/xrcDialogs.cpp (revision 7394)
>>> +++ ui/xrcDialogs.cpp (working copy)
>>> @@ -13,6 +13,14 @@
>>> #include <wx/xrc/xmlres.h>
>>> #include <wx/xrc/xh_all.h>
>>>
>>> +#if wxCHECK_VERSION(2,8,5) && wxABI_VERSION >= 20805
>>> + #define XRC_ADD_FILE(name, data, size, mime) \
>>> + wxMemoryFSHandler::AddFileWithMimeType(name, data, size, mime)
>>> +#else
>>> + #define XRC_ADD_FILE(name, data, size, mime) \
>>> + wxMemoryFSHandler::AddFile(name, data, size)
>>> +

Re: [GENERAL] Postgresql not using an index

Hello

please, send EXPLAIN ANALYZE output.

regards
Pavel Stehule

2008/8/1 Marc Cuypers <m.cuypers@mgvd.be>:
> Hi,
>
> I'm using postgres 7.4 and bacula 1.38 on debian.
>
> In the bacula database there is a table named 'file' which has about 2.5
> million rows.
> In this table there is a field 'jobid' which is indexed.
> The index is created with the following command:
> CREATE INDEX file_jobid_idx ON file USING btree (jobid);
>
> The query:
> SELECT * from file where jobid=2792
>
> does a full scan and to my opinion it doesn't use the index.
> I already did a VACUUM ANALYZE on the database.
>
>
> Somebody an idea?
>
> EXPLAIN tells the following:
> Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual
> time=103215.145..161153.664 rows=1 loops=1)
> Filter: (jobid = 2792)
> Total runtime: 161154.734 ms
>
> The Verbose Explain tells the following:
> {SEQSCAN
> :startup_cost 0.00
> :total_cost 707683.30
> :plan_rows 207562
> :plan_width 110
> :targetlist (
> {TARGETENTRY
> :resdom
> {RESDOM
> :resno 1
> :restype 23
> :restypmod -1
> :resname fileid
> :ressortgroupref 0
> :resorigtbl 440806231
> :resorigcol 1
> :resjunk false
> }
>
> :expr
> {VAR
> :varno 1
> :varattno 1
> :vartype 23
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 1
> }
> }
>
> {TARGETENTRY
> :resdom
> {RESDOM
> :resno 2
> :restype 23
> :restypmod -1
> :resname fileindex
> :ressortgroupref 0
> :resorigtbl 440806231
> :resorigcol 2
> :resjunk false
> }
>
> :expr
> {VAR
> :varno 1
> :varattno 2
> :vartype 23
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 2
> }
> }
>
> {TARGETENTRY
> :resdom
> {RESDOM
> :resno 3
> :restype 23
> :restypmod -1
> :resname jobid
> :ressortgroupref 0
> :resorigtbl 440806231
> :resorigcol 3
> :resjunk false
> }
>
> :expr
> {VAR
> :varno 1
> :varattno 3
> :vartype 23
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 3
> }
> }
>
> {TARGETENTRY
> :resdom
> {RESDOM
> :resno 4
> :restype 23
> :restypmod -1
> :resname pathid
> :ressortgroupref 0
> :resorigtbl 440806231
> :resorigcol 4
> :resjunk false
> }
>
> :expr
> {VAR
> :varno 1
> :varattno 4
> :vartype 23
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 4
> }
> }
>
> {TARGETENTRY
> :resdom
> {RESDOM
> :resno 5
> :restype 23
> :restypmod -1
> :resname filenameid
> :ressortgroupref 0
> :resorigtbl 440806231
> :resorigcol 5
> :resjunk false
> }
>
> :expr
> {VAR
> :varno 1
> :varattno 5
> :vartype 23
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 5
> }
> }
>
> {TARGETENTRY
> :resdom
> {RESDOM
> :resno 6
> :restype 23
> :restypmod -1
> :resname markid
> :ressortgroupref 0
> :resorigtbl 440806231
> :resorigcol 6
> :resjunk false
> }
>
> :expr
> {VAR
> :varno 1
> :varattno 6
> :vartype 23
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 6
> }
> }
>
> {TARGETENTRY
> :resdom
> {RESDOM
> :resno 7
> :restype 25
> :restypmod -1
> :resname lstat
> :ressortgroupref 0
> :resorigtbl 440806231
> :resorigcol 7
> :resjunk false
> }
>
> :expr
> {VAR
> :varno 1
> :varattno 7
> :vartype 25
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 7
> }
> }
>
> {TARGETENTRY
> :resdom
> {RESDOM
> :resno 8
> :restype 25
> :restypmod -1
> :resname md5
> :ressortgroupref 0
> :resorigtbl 440806231
> :resorigcol 8
> :resjunk false
> }
>
> :expr
> {VAR
> :varno 1
> :varattno 8
> :vartype 25
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 8
> }
> }
> )
>
> :qual (
> {OPEXPR
> :opno 96
> :opfuncid 65
> :opresulttype 16
> :opretset false
> :args (
> {VAR
> :varno 1
> :varattno 3
> :vartype 23
> :vartypmod -1
> :varlevelsup 0
> :varnoold 1
> :varoattno 3
> }
>
> {CONST
> :consttype 23
> :constlen 4
> :constbyval true
> :constisnull false
> :constvalue 4 [ -24 10 0 0 ]
> }
> )
> }
> )
>
> :lefttree <>
> :righttree <>
> :initPlan <>
> :extParam ()
>
> :allParam ()
>
> :nParamExec 0
> :scanrelid 1
> }
>
> Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual
> time=103215.145..161153.664 rows=1 loops=1)
> Filter: (jobid = 2792)
> Total runtime: 161154.734 ms
>
>
> --
> Best regards,
>
> Marc
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
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] Plans for 8.4

Henry B. Hotz wrote:
>
> On Jul 31, 2008, at 7:58 AM, Magnus Hagander wrote:
>
>> Stephen Frost wrote:
>>> * Henry B. Hotz (hbhotz@oxy.edu) wrote:
>>>> I'm making no promises, but what would people think of a hostgss hba
>>>> option?
>>>
>>> As described, sounds like a win to me. It'd be very nice to be able to
>>> just use GSSAPI encryption on the link. That, combined w/ Magnus' work
>>> on username/princ mappings, would really bring PostgreSQL up to date wrt
>>> GSSAPI support.
>>
>> Yeah, +1 on this feature, it would be quite useful.
>>
>>
>>> It'd really be great to have this support in the ODBC and JDBC drivers
>>> too.. I think in JDBC it might 'just work', I'm less sure about ODBC.
>>
>> ODBC will need hackery I think. They use libpq for authentication only,
>> but have their own SSL code and such. I do think ODBC would be a fairly
>> major point to it being a success, though, so it'd be good if a plan
>> could be secured for it. But it's not a showstopper, of course.
>
> I don't know enough about ODBC. If ODBC does SSL independently of PG
> then it requires thought by someone who understands ODBC.

I just meant please consider coordinating with the ODBC folks to make
sure it gets in there as well - and in time for the same release.

>>> As a practical question- would you really need a seperate explicit
>>> pg_hba option for it? It'd be nice to be able to require it, if
>>> desired, but that strikes me as more sensible as an option to the 'gss'
>>> auth mechanism?
>>
>> Yeah, if we can get rid of that, that'd be good. The stuff I'm working
>> on will allow us to have multiple parameters for each row in name/value
>> pairs, so if we could use that, it'd be better. (I've been considering
>> changing how host/hostssl work that way as well - by having a parameter
>> similar to what we have on the client side with sslmode=...)
>>
>> A thought that I came across - is it even possible to use GSSAPI
>> encryption *without* using GSSAPI authentication? If not, it really
>> seems like it should belong more in the parameter part of the field.
>> Since in that case it is also not possible to enable encryption *before*
>> authentication, or is it?
>
> You're on the right track. My problem isn't the hba file parsing at all.
>
> My problem is the interaction between the buffering logic and the
> encrypted I/O routines. The technical issue is that to make a GSSAPI
> security layer independent of SSL you need to invent a whole new
> buffering layer. That's a lot of work, and it only buys you the ability
> to do both SSL and GSSAPI at the same time. That doesn't seem worth it.

Yeah, there seems to be no general point in that.

However, implementing a layer there might have other benefits. Such as
being able to use other SSL implementations (right now we only do
OpenSSL. There has been talk about GnuTLS, and it would be good to be
able to do schannel)

> The code being affected is what's currently configured in column 1 of
> hba. The ability to use the new capability requires that SSL *NOT* be
> configured in column 1 for the relevant client addresses. In short, no,
> it doesn't make sense to make it an option to the gss authentication
> method, even though it requires it. If we make it an option to the gss
> authentication method it would still need to act like it was specified
> in column 1, which would be confusing.

Does this hold even if we move the "hostssl" stuff into a parameter "at
the end"? I was thinking maybe something like:
host all all 0.0.0.0/0 md5 ssl=require
host all all 0.0.0.0/0 gss ssl=forbid gssencrypt=require

(you get the idea)

> GSSAPI security layers are negotiated after the authentication (or at
> least after the start of authentication). There are GSSAPI status flags
> that indicate if the security layer is available yet. The GSSAPI
> security layer code would check those flags and gss_wrap() or not
> accordingly. (-: There's a flush() or two from my original patch that
> will need to be added back in, otherwise we'll encrypt a message that
> tells the other end how to decrypt messages. Not a big deal. ;-)

Ok, makes sense.

//Magnus

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

[SQL] How to change a view's owner in postgres

Dear All,

How to change a view's owner in postgres?

thanks in advance:
Anoop

Re: [pgadmin-hackers] Minimum height of server status dialog

Guillaume Lelarge wrote:
>> The hotkey stuff I was working on already, but I'll just use your patch
>> instead. It seems to be working fine in on my machine as well, so I'll
>> go ahead and commit it. One thing first though...
>>
>> When I ran embed-xrc, I got a diff containing:
>> Index: ui/xrcDialogs.cpp
>> ===================================================================
>> --- ui/xrcDialogs.cpp (revision 7394)
>> +++ ui/xrcDialogs.cpp (working copy)
>> @@ -13,6 +13,14 @@
>> #include <wx/xrc/xmlres.h>
>> #include <wx/xrc/xh_all.h>
>>
>> +#if wxCHECK_VERSION(2,8,5) && wxABI_VERSION >= 20805
>> + #define XRC_ADD_FILE(name, data, size, mime) \
>> + wxMemoryFSHandler::AddFileWithMimeType(name, data, size, mime)
>> +#else
>> + #define XRC_ADD_FILE(name, data, size, mime) \
>> + wxMemoryFSHandler::AddFile(name, data, size)
>> +

Re: [pgadmin-hackers] Minimum height of server status dialog

Magnus Hagander a écrit :
> Guillaume Lelarge wrote:
>> Magnus Hagander a écrit :
>>> Can somebody explain why there appears to be a minimum height on the
>>> server status dialog that corresponds (on $customers screen at least) to
>>> about 20 active connections?
>>>
>>> I have <10 on each server right now, but would like to fit three windows
>>> over each other vertically, but there's no way to make the window small
>>> enough. I do, however, get lots and lots of whitespace...
>>>
>>> (the locks and logfile tabs go into scrolling mode on the other tabs, so
>>> I don't see why those would be the reason either).
>>>
>>> If there's no reason, any objections to decreasing it (assuming I can
>>> figure out where it's set)?
>>>
>> Here is a patch that fixes your two issues (the one on this mail and the
>> one on your " "Dangerous" hotkey in server status dialog " mail). It
>> should work on 1.8 branch, but I didn't actually try it there. I also
>> didn't check on Windows or MacOS X but none of the changes should affect
>> badly one of those.
>
> Thanks!
>
> I don't know if I hate wx more, or if wx hates me more, but I really
> couldn't make the layout stuff do what I wanted :)
>

Working to fix some dialogs makes me learn a few things about xrc files.
So, now, I hate xrc files less. Anyways, I'm still not a wxWidgets fan.

> The hotkey stuff I was working on already, but I'll just use your patch
> instead. It seems to be working fine in on my machine as well, so I'll
> go ahead and commit it. One thing first though...
>
> When I ran embed-xrc, I got a diff containing:
> Index: ui/xrcDialogs.cpp
> ===================================================================
> --- ui/xrcDialogs.cpp (revision 7394)
> +++ ui/xrcDialogs.cpp (working copy)
> @@ -13,6 +13,14 @@
> #include <wx/xrc/xmlres.h>
> #include <wx/xrc/xh_all.h>
>
> +#if wxCHECK_VERSION(2,8,5) && wxABI_VERSION >= 20805
> + #define XRC_ADD_FILE(name, data, size, mime) \
> + wxMemoryFSHandler::AddFileWithMimeType(name, data, size, mime)
> +#else
> + #define XRC_ADD_FILE(name, data, size, mime) \
> + wxMemoryFSHandler::AddFile(name, data, size)
> +

[COMMITTERS] pgsql: Move ident authentication code into auth.c along with the other

Log Message:
-----------
Move ident authentication code into auth.c along with the other authenciation
routines, leaving hba.c to deal only with processing the HBA specific files.

Modified Files:
--------------
pgsql/src/include/libpq:
hba.h (r1.47 -> r1.48)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/libpq/hba.h?r1=1.47&r2=1.48)
pgsql/src/backend/libpq:
auth.c (r1.165 -> r1.166)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/libpq/auth.c?r1=1.165&r2=1.166)
hba.c (r1.165 -> r1.166)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/libpq/hba.c?r1=1.165&r2=1.166)

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

[GENERAL] Postgresql not using an index

Hi,

I'm using postgres 7.4 and bacula 1.38 on debian.

In the bacula database there is a table named 'file' which has about 2.5
million rows.
In this table there is a field 'jobid' which is indexed.
The index is created with the following command:
CREATE INDEX file_jobid_idx ON file USING btree (jobid);

The query:
SELECT * from file where jobid=2792

does a full scan and to my opinion it doesn't use the index.
I already did a VACUUM ANALYZE on the database.

Somebody an idea?

EXPLAIN tells the following:
Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual
time=103215.145..161153.664 rows=1 loops=1)
Filter: (jobid = 2792)
Total runtime: 161154.734 ms

The Verbose Explain tells the following:
{SEQSCAN
:startup_cost 0.00
:total_cost 707683.30
:plan_rows 207562
:plan_width 110
:targetlist (
{TARGETENTRY
:resdom
{RESDOM
:resno 1
:restype 23
:restypmod -1
:resname fileid
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 1
:resjunk false
}

:expr
{VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
}

{TARGETENTRY
:resdom
{RESDOM
:resno 2
:restype 23
:restypmod -1
:resname fileindex
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 2
:resjunk false
}

:expr
{VAR
:varno 1
:varattno 2
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
}

{TARGETENTRY
:resdom
{RESDOM
:resno 3
:restype 23
:restypmod -1
:resname jobid
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 3
:resjunk false
}

:expr
{VAR
:varno 1
:varattno 3
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 3
}
}

{TARGETENTRY
:resdom
{RESDOM
:resno 4
:restype 23
:restypmod -1
:resname pathid
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 4
:resjunk false
}

:expr
{VAR
:varno 1
:varattno 4
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 4
}
}

{TARGETENTRY
:resdom
{RESDOM
:resno 5
:restype 23
:restypmod -1
:resname filenameid
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 5
:resjunk false
}

:expr
{VAR
:varno 1
:varattno 5
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 5
}
}

{TARGETENTRY
:resdom
{RESDOM
:resno 6
:restype 23
:restypmod -1
:resname markid
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 6
:resjunk false
}

:expr
{VAR
:varno 1
:varattno 6
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 6
}
}

{TARGETENTRY
:resdom
{RESDOM
:resno 7
:restype 25
:restypmod -1
:resname lstat
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 7
:resjunk false
}

:expr
{VAR
:varno 1
:varattno 7
:vartype 25
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 7
}
}

{TARGETENTRY
:resdom
{RESDOM
:resno 8
:restype 25
:restypmod -1
:resname md5
:ressortgroupref 0
:resorigtbl 440806231
:resorigcol 8
:resjunk false
}

:expr
{VAR
:varno 1
:varattno 8
:vartype 25
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 8
}
}
)

:qual (
{OPEXPR
:opno 96
:opfuncid 65
:opresulttype 16
:opretset false
:args (
{VAR
:varno 1
:varattno 3
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 3
}

{CONST
:consttype 23
:constlen 4
:constbyval true
:constisnull false
:constvalue 4 [ -24 10 0 0 ]
}
)
}
)

:lefttree <>
:righttree <>
:initPlan <>
:extParam ()

:allParam ()

:nParamExec 0
:scanrelid 1
}

Seq Scan on file (cost=0.00..707683.30 rows=207562 width=110) (actual
time=103215.145..161153.664 rows=1 loops=1)
Filter: (jobid = 2792)
Total runtime: 161154.734 ms

--
Best regards,

Marc

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

Re: [pgadmin-hackers] Minimum height of server status dialog

Guillaume Lelarge wrote:
> Magnus Hagander a écrit :
>> Can somebody explain why there appears to be a minimum height on the
>> server status dialog that corresponds (on $customers screen at least) to
>> about 20 active connections?
>>
>> I have <10 on each server right now, but would like to fit three windows
>> over each other vertically, but there's no way to make the window small
>> enough. I do, however, get lots and lots of whitespace...
>>
>> (the locks and logfile tabs go into scrolling mode on the other tabs, so
>> I don't see why those would be the reason either).
>>
>> If there's no reason, any objections to decreasing it (assuming I can
>> figure out where it's set)?
>>
>
> Here is a patch that fixes your two issues (the one on this mail and the
> one on your " "Dangerous" hotkey in server status dialog " mail). It
> should work on 1.8 branch, but I didn't actually try it there. I also
> didn't check on Windows or MacOS X but none of the changes should affect
> badly one of those.

Thanks!

I don't know if I hate wx more, or if wx hates me more, but I really
couldn't make the layout stuff do what I wanted :)

The hotkey stuff I was working on already, but I'll just use your patch
instead. It seems to be working fine in on my machine as well, so I'll
go ahead and commit it. One thing first though...

When I ran embed-xrc, I got a diff containing:
Index: ui/xrcDialogs.cpp
===================================================================
--- ui/xrcDialogs.cpp (revision 7394)
+++ ui/xrcDialogs.cpp (working copy)
@@ -13,6 +13,14 @@
#include <wx/xrc/xmlres.h>
#include <wx/xrc/xh_all.h>

+#if wxCHECK_VERSION(2,8,5) && wxABI_VERSION >= 20805
+ #define XRC_ADD_FILE(name, data, size, mime) \
+ wxMemoryFSHandler::AddFileWithMimeType(name, data, size, mime)
+#else
+ #define XRC_ADD_FILE(name, data, size, mime) \
+ wxMemoryFSHandler::AddFile(name, data, size)
+

Re: [SQL] Function returning setof taking parameters from another table

Marcin Stępnicki wrote:

> So far the only method I can think of is to use union all with
> different parametrs, like:
>
> select * from f_test(123)
> union all
> select * from f_test(124)
> union all
> select * from f_test(125);
>
> But it is not flexible, I'd like to have parameters stored in another table.
>
> I can also write a pl/pg function, create a string like this with
> unions and EXECUTE it. However, it seems ugly to me.

One method is a PL/PgSQL set-returning function that loops over the
arguments, executes the function for each, and uses an inner loop to
RETURN NEXT the results.

Another way is to use Pg's support for expanding set-returning functions
in SELECT clauses. In simple cases you can get effects similar to the
use of a series of UNION operations. For example:

CREATE TYPE intpair AS (first INTEGER, last INTEGER);

--
-- Make a function that returns `$1' pairs of integers from 1 to $1
--
CREATE FUNCTION gsp (INTEGER) RETURNS SETOF intpair AS $$
SELECT generate_series(1,$1), generate_series(1,$1);
$$ LANGUAGE SQL;

--
-- Return a set of integer pairs from (1,1) to (4,4)
--
SELECT gsp(4);

--
-- Just run this one to see what it does.
--
SELECT x, gsp(x) FROM generate_series(1,10) as x;

--
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: [PERFORM] Nls sorting in Postgresql-8.3.3

Praveen wrote:
> Hello,
> I installed postgresql-8.3.3 in our local server with option --enable-nls . After successful installion , I create database and import data.But I am not aware how to use nls sort in this postgresql-8.3.3 .
> Please tell me syntax how to use nls sort in query , if some one know.

What is "nls sort"? What do you expect --enable-nls to do? It looks like
it's for multi-language message display rather than sorting.

The locale options are already built-in.

--
Richard Huxton
Archonet Ltd

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

[PERFORM] Nls sorting in Postgresql-8.3.3

Hello,

I installed postgresql-8.3.3 in our local server  with option  --enable-nls . After successful installion , I create database and import data.But I am not aware how to use nls sort in this postgresql-8.3.3 .

Please tell me  syntax how to use nls sort in query , if some one know.

Thanks in advance.

Praveen Malik.

Re: [pgadmin-hackers] Google Summer of Code Projects

Dave Page a écrit :
> [...]
> It's important that these projects are given as much support as
> possible to ensure that we can attract future GSoC sponsorship from
> Google in the future to help us continue to add cool new features to
> pgAdmin, so please take some time to try of the binaries, review the
> code and give as much useful feedback as possible.
>

I'll work on these this afternoon. I wish to finish my own project before :)

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

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

Re: [pgadmin-hackers] Minimum height of server status dialog

Index: pgadmin/frm/frmStatus.cpp
===================================================================
--- pgadmin/frm/frmStatus.cpp (révision 7394)
+++ pgadmin/frm/frmStatus.cpp (copie de travail)
@@ -29,7 +29,8 @@
BEGIN_EVENT_TABLE(frmStatus, pgDialog)
EVT_BUTTON(wxID_REFRESH, frmStatus::OnRefresh)
EVT_BUTTON(wxID_CLOSE, frmStatus::OnCloseBtn)
- EVT_BUTTON(wxID_CANCEL, frmStatus::OnCancelBtn)
+ EVT_BUTTON(wxID_CANCEL, frmStatus::OnCloseBtn)
+ EVT_BUTTON(XRCID("btnCancelSt"), frmStatus::OnCancelBtn)
EVT_BUTTON(wxID_STOP, frmStatus::OnTerminateBtn)
EVT_BUTTON(XRCID("btnCommit"), frmStatus::OnCommit)
EVT_BUTTON(XRCID("btnRollback"), frmStatus::OnRollback)
@@ -101,8 +102,6 @@
btnTerminateLk = CTRL_BUTTON("btnTerminateLk");
btnCommit = CTRL_BUTTON("btnCommit");
btnRollback = CTRL_BUTTON("btnRollback");
- ChangeButtonId(btnCancelSt, wxID_CANCEL, _("Cancel"));
- ChangeButtonId(btnCancelLk, wxID_CANCEL, _("Cancel"));
ChangeButtonId(btnTerminateSt, wxID_STOP, _("Terminate"));
ChangeButtonId(btnTerminateLk, wxID_STOP, _("Terminate"));

Index: pgadmin/ui/frmStatus.xrc
===================================================================
--- pgadmin/ui/frmStatus.xrc (révision 7394)
+++ pgadmin/ui/frmStatus.xrc (copie de travail)
@@ -2,7 +2,7 @@
<resource>
<object class="wxDialog" name="frmStatus">
<title>Database Status</title>
- <size>400,300d</size>
+ <size>200,100d</size>
<style>wxDEFAULT_DIALOG_STYLE|wxCAPTION|wxSYSTEM_MENU|wxRESIZE_BORDER|wxRESIZE_BOX|wxTHICK_FRAME|wxMINIMIZE_BOX</style>
<object class="wxFlexGridSizer">
<cols>1</cols>
@@ -10,7 +10,6 @@
<object class="sizeritem">
<object class="wxNotebook" name="nbStatus">
<pos>2,24d</pos>
- <size>395,273d</size>
<object class="notebookpage">
<label>Status</label>
<object class="wxPanel" name="pnlStatus">
@@ -216,7 +215,7 @@
<border>5</border>
</object>
<object class="sizeritem">
- <object class="wxButton" name="wxID_CLOSE">
+ <object class="wxButton" name="wxID_CANCEL">
<label>Close</label>
<pos>342,4d</pos>
</object>
Magnus Hagander a écrit :
> Can somebody explain why there appears to be a minimum height on the
> server status dialog that corresponds (on $customers screen at least) to
> about 20 active connections?
>
> I have <10 on each server right now, but would like to fit three windows
> over each other vertically, but there's no way to make the window small
> enough. I do, however, get lots and lots of whitespace...
>
> (the locks and logfile tabs go into scrolling mode on the other tabs, so
> I don't see why those would be the reason either).
>
> If there's no reason, any objections to decreasing it (assuming I can
> figure out where it's set)?
>

Here is a patch that fixes your two issues (the one on this mail and the
one on your " "Dangerous" hotkey in server status dialog " mail). It
should work on 1.8 branch, but I didn't actually try it there. I also
didn't check on Windows or MacOS X but none of the changes should affect
badly one of those.

Cheers.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

Re: [GENERAL] Cursor Error

Bob Pawley wrote:
> Is it allowed to declare a cursor in this manner??
>
> Declare
> procgraphic cursor for select p_id.p_id.process_id
> from p_id.p_id, processes_count
> where p_id.p_id.p_id_id = processes_count.p_id_id;

Using DECLARE instead of OPEN? Yes, but that won't somehow make a cursor
involving a join updatable. See:

http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html

DECLARE and OPEN do not have exactly the same meaning, as explained by
the above documentation. To use a cursor defined with DECLARE you must
use OPEN - see section 38.7.2.3 ("Opening a Bound Cursor") of the
documentation.

--
Craig Ringer

--
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] Copy fails

Abraham, Danny wrote:
> I am loading a huge file using C, STDIN

Using C?

Have you written a C program using libpq to load some data, which it
reads from its stdin?

Or do you mean COPY FROM STDIN ?

Something else?

Perhaps if you provided a clearer and more complete explanation of your
problem you might get a more useful answer.

> The program fails immediately on "canceling statement due to statement
> timeout"

Do you have a statement timeout set in postgresql.conf ?

--
Craig Ringer

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

[HACKERS] NDirectFileRead and Write

Hello,

I'd like to use NDirectFileRead and NDirectFileWrite statistics counters
for counting reads and writes in BufFile. They are defined, but not used
now. BufFile is used for tuple sorting or materializing, so we could use
NDirectFileRead/Write to retrieve how many I/Os are done in temp tablespace.

We can get the values of NDirectFileRead/Write when we enable
log_statement_stats, log_parser_stats, log_planner_stats or
log_executor_stats. (They show always 0 for NDirectFile R/W now.)

I have a plan to store the values shown by log_xxx_stats into
pg_stat_statements, that is per-query statistics information collector
I'm developing. The combination of NDirectFile R/W and pg_stat_statements
have an advantage to detect which sql uses large tuple sorting.
And after we find such queries, we can turn trace_sort on and examine
how amount of work_mem is needed by such queries. We could save time
for tuning queries and reading server logs.

-- The patch would be very trivial ;-)

Index: src/backend/storage/file/buffile.c
===================================================================
--- src/backend/storage/file/buffile.c (HEAD)
+++ src/backend/storage/file/buffile.c (working copy)
@@ -238,6 +238,7 @@
file->nbytes = 0;
file->offsets[file->curFile] += file->nbytes;
/* we choose not to advance curOffset here */
+ NDirectFileRead++;
}

/*
@@ -300,6 +301,7 @@
file->offsets[file->curFile] += bytestowrite;
file->curOffset += bytestowrite;
wpos += bytestowrite;
+ NDirectFileWrite++;
}
file->dirty = false;

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] eliminating records not in (select id ... so SLOW?

On Thu, 31 Jul 2008 21:37:39 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Ivan Sergio Borgonovo <mail@webthatworks.it> writes:
> > I'm doing something like:
> > delete from table1 where id not in (select id from table2).
> > table1 contains ~1M record table2 contains ~ 600K record and id
> > is unique.

> That's going to pretty much suck unless you've got work_mem set
> high enough to allow a "hashed subplan" plan --- which is likely
> to require tens of MB for this case, I don't recall exactly what

Thanks.

> the per-row overhead is. Experiment until EXPLAIN tells you it'll
> use a hashed subplan.

explain delete from catalog_categoryitem where ItemID not in (select
ItemID from catalog_items);

Well I reached 3Gb of work_mem and still I got:

"Seq Scan on catalog_categoryitem (cost=31747.84..4019284477.13
rows=475532 width=6)"
" Filter: (NOT (subplan))"
" SubPlan"
" -> Materialize (cost=31747.84..38509.51 rows=676167 width=8)"
" -> Seq Scan on catalog_items (cost=0.00..31071.67
rows=676167 width=8)"

I've this too:
alter table catalog_items cluster on catalog_items_pkey;
should I drop it?

This is just a dev box. I loaded the 2 tables with 2 not coherent
set of data just to play with, before adding all the pk/fk I need.
I could just truncate the tables and reload them from coherent
sources.

But what if I *really* had to execute that query?
Any other magic I could play to speed it up?

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

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