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)
>> +
Friday, August 1, 2008
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 :)
>
> 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.
-----------
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.
> 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.
> 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.
>
> [...]
> 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)?
>
===================================================================
--- 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;
> 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
> 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:
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
Thursday, July 31, 2008
Re: [HACKERS] Review: DTrace probes (merged version) ver_03
Alvaro Herrera wrote:
> I was checking the DTrace docs for other reasons and I came across this,
> which maybe can be useful here:
>
> http://docs.sun.com/app/docs/doc/817-6223/chp-xlate?a=view
>
>
Yes, I think using the translator is the best approach to expose
internal structures in a stable manner.
> I was checking the DTrace docs for other reasons and I came across this,
> which maybe can be useful here:
>
> http://docs.sun.com/app/docs/doc/817-6223/chp-xlate?a=view
>
>
Yes, I think using the translator is the best approach to expose
internal structures in a stable manner.
--
Robert Lor Sun Microsystems
Austin, USA http://sun.com/postgresql
--
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] Review: DTrace probes (merged version) ver_03
Alvaro Herrera wrote:
> Here's what I have. Please confirm that this compiles for you.
>
I made some changes to the sed script so it works with the sed on
Solaris & OS X. I tested this patch on both Solaris and OS X with DTrace
enabled and disabled and also verified that the sed script works with
GNU sed. I hope this is the final change for this patch. Thanks for
catching all the issues, and my bad for not testing with DTrace disabled.
> Here's what I have. Please confirm that this compiles for you.
>
I made some changes to the sed script so it works with the sed on
Solaris & OS X. I tested this patch on both Solaris and OS X with DTrace
enabled and disabled and also verified that the sed script works with
GNU sed. I hope this is the final change for this patch. Thanks for
catching all the issues, and my bad for not testing with DTrace disabled.
>
> ------------------------------------------------------------------------
>
>
--
Robert Lor Sun Microsystems
Austin, USA http://sun.com/postgresql
Re: [GENERAL] Declaring constants in SQL
The last part got scrambled, should read like this:
(...)
Use it like this:
SELECT * FROM foo WHERE foo_id > myval();
Or, for the case at hand, an example in sql:
CREATE FUNCTION my_colors()
RETURNS text[] AS
$$ SELECT ARRAY['red','green','blue'] $$
LANGUAGE 'sql' IMMUTABLE;
Use it like this:
SELECT * FROM foo WHERE color = ANY(my_colors());
Regards
Erwin
--
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] Declaring constants in SQL
On Jul 30, 10:53 pm, richard.broer...@gmail.com ("Richard Broersma")
wrote:
> On Wed, Jul 30, 2008 at 1:16 PM, EXT-Rothermel, Peter M
> <Peter.M.Rother...@boeing.com> wrote:
> > Is there any means like (#define or DECLARE ) where I can write SQL like
wrote:
> On Wed, Jul 30, 2008 at 1:16 PM, EXT-Rothermel, Peter M
> <Peter.M.Rother...@boeing.com> wrote:
> > Is there any means like (#define or DECLARE ) where I can write SQL like
(...)
> CREATE VIEW primary_colors_foos AS
> SELECT * FROM foo
> WHERE color = ANY( SELECT colorid
> FROM Colors
> WHERE colorname = ANY( 'red', 'blue', 'yellow' ));
Or even:
CREATE VIEW primary_color_foos AS
SELECT foo.* FROM foo JOIN color c USING (color_id)
WHERE c.colorname IN ('red', 'blue', 'yellow' );
If you have some constant values you need all over the place, you can
also resort to functions, which you can use much like CONSTANTs:
CREATE FUNCTION my_val()
RETURNS integer AS
$BODY$
BEGIN
RETURN 21;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE;
Or, for the case at hand, an example in sql:
SELECT * FROM foo WHERE foo_id > myval();
CREATE FUNCTION my_colors()
RETURNS text[] AS
$$ SELECT ARRAY['red','green','blue'] $$
LANGUAGE 'sql' IMMUTABLE;
Use it like this:
SELECT * FROM foo WHERE color = ANY(myval());
Regards
Erwin
--
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] Problem with ORDER BY and DISTINCT ON
At 03:51 PM 7/31/2008, Tom Lane wrote:
>Steve Midgley <public@misuse.org> writes:
> > At 07:29 AM 7/16/2008, Tom Lane wrote:
> >> I think what is happening is that ORDER BY knows that and gets rid
> of
> >> the duplicate entries while DISTINCT ON fails to do so.
>
> > Of course removing the duplicate from both areas is the correct
> > solution and I broke down and hacked that into the auto-sql-writing
>
> > code and so my immediate problem is solved. I'm happy to file this
> as a
> > ticket for Pg (please point me to your ticket tool as I've never
> used
> > it). This is not a very big deal but Pg has such a high compliance
> with
> > wacky-but-valid SQL it does seem like it should be fixed just
> because.
>
>I've applied a patch for this to CVS HEAD. I doubt we'll try to fix
>it
>in the back branches, though --- it's too much of a corner case to be
>worth taking any risk of breaking other stuff.
>
> regards, tom lane
Hey Tom,
>Steve Midgley <public@misuse.org> writes:
> > At 07:29 AM 7/16/2008, Tom Lane wrote:
> >> I think what is happening is that ORDER BY knows that and gets rid
> of
> >> the duplicate entries while DISTINCT ON fails to do so.
>
> > Of course removing the duplicate from both areas is the correct
> > solution and I broke down and hacked that into the auto-sql-writing
>
> > code and so my immediate problem is solved. I'm happy to file this
> as a
> > ticket for Pg (please point me to your ticket tool as I've never
> used
> > it). This is not a very big deal but Pg has such a high compliance
> with
> > wacky-but-valid SQL it does seem like it should be fixed just
> because.
>
>I've applied a patch for this to CVS HEAD. I doubt we'll try to fix
>it
>in the back branches, though --- it's too much of a corner case to be
>worth taking any risk of breaking other stuff.
>
> regards, tom lane
Hey Tom,
That's really great - thanks. I'm impressed how quickly you are fixing
this obscure issue. I came from MS SQL and it would be hard for me to
put into words how much of a better job you all are doing on Pg.
Best,
Steve
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [pgsql-www] Email search failure
Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
>> Why is the email below now appearing in a search?
>
> Probably because nothing has gotten indexed for a month or more.
> Whoever is supposed to maintain the archive indexer has been
> on vacation since it broke ...
> Bruce Momjian <bruce@momjian.us> writes:
>> Why is the email below now appearing in a search?
>
> Probably because nothing has gotten indexed for a month or more.
> Whoever is supposed to maintain the archive indexer has been
> on vacation since it broke ...
That would be Magnus and you are correct. He just got back. The problem
(last I checked) is an issue with Russian emails.
Sincerely,
Joshua D. Drake
>
> regards, tom lane
>
--
Sent via pgsql-www mailing list (pgsql-www@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-www
Re: [HACKERS] Copy storage parameters on CREATE TABLE LIKE/INHERITS
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> There are a couple of other things that need to be thought about:
> * LIKE is intended to copy a table as a *portion* of another table;
You're absolutely right. I just thought it's a *better default* behavior
because LIKE and INHERITS are often used in single inheritance, but
in definition they declare columns, not whole tables.
In other words, we don't have an ability of copying tables as units...
However, I think we'd better to have an ability to copy reloptions easily.
There were requests about adding a configuration parameter to modify
default fillfactor. I think reloption-cloning would be a solution about it.
How about adding a new WITH-LIKE syntax?
CREATE TABLE newtbl ( ... ) WITH (LIKE "template-table")
It is expanded to an option array as below:
SELECT 'WITH (' || array_to_string(
array_append(reloptions, 'OIDS=' || relhasoids), ',') || ')'
FROM pg_class
WHERE oid = "template-table";
> I think therefore that having LIKE copy anything "global" to a table,
> such as tablespace or reloptions, is fundamentally wrongheaded. What
> will you do about conflicts? The same is true for inheritance cases,
> since a table can inherit from multiple parents.
Currently I uses the first setting found in multiple tables
and directly specified options have the highest priority.
For example, setting are used in order of [A] -> [B] -> [C].
Conflicted parameters are ignored silently for now.
CREATE TABLE newtbl (LIKE [B], LIKE [C]) WITH ([A])
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] 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?
> 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 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
Re: [pgsql-es-ayuda] sobre savepoints
He estado mirando pgloader pero no parece diseñado para lo que yo necesito si no para
importar ficheros csv o bueno otros flat files configurables, al caso supongo que aunque
exportase mis tablas (que es donde los triggers crean la informacion) a un formato que el
gustase tendria que crear savepoints si quiero permitir algun fallback igual que ya hago
yo. Es un poquillo irritante que no sea pueda modificar este parametro en postgresql :\
importar ficheros csv o bueno otros flat files configurables, al caso supongo que aunque
exportase mis tablas (que es donde los triggers crean la informacion) a un formato que el
gustase tendria que crear savepoints si quiero permitir algun fallback igual que ya hago
yo. Es un poquillo irritante que no sea pueda modificar este parametro en postgresql :\
Alvaro Herrera escribió:
> Linos escribió:
>
>> begin ----- savepoint ---- insert ---- releasesavepoint ---- savepoint
>> ---- insert ----- releasesavepoint ---- savepoint --- insert(error) ---
>> rollback to savepoint --- update --- releasesavepoint --- 20000 registros
>> mas ---- commit
>>
>> Lo cual a traves de una linea lenta ralentiza considerablemente. Supongo
>> que debe haber alguna manera mejor de hacer esto que evidentemente yo no
>> conozco, se puede desactivar este modo para que la transaccion siga con
>> un error hasta que yo haga un rollback especifico?
>
> No. Todos los errores dejan la transaccion en estado "abortado", del
> cual solo puedes salir abortandola completamente o bien con ROLLBACK TO
> savepoint.
>
> (Efectivamente lo que el codigo hace es un "rollback automatico"
> internamente)
>
>> otra manera que habia pensado es hacer una funcion para los inserts (q
>> cambiara a update si existe el registro) pero las columnas cambian no
>> solamente para cada tabla si no a veces en cada insert, se generan
>> con unos triggers automaticamente y no se si se podria hacer o incluso
>> si interesaria hacerlo. Me podrian echar un cable con esto? gracias.
>
> Si el problema es la cantidad extra de ordenes que tienes que enviar
> desde el cliente, creo que seria una ganancia enorme hacerlo en una
> funcion del lado del servidor, usando bloques EXCEPTION. Ahora, tienes
> que tener en cuenta que EXCEPTION crea un nuevo savepoint cada vez, asi
> que si la ralentizacion viene por el lado de crear los savepoints (lo
> cual no seria raro, porque los savepoints son relativamente lentos)
> entonces va a seguir siendo un cuello de botella.
>
> Lo otro que podrias hacer, quizas, es intentar con PGLoader.
>
--
TIP 7: no olvides aumentar la configuración del "free space map"
Re: [ADMIN] Postgres User Can Login without a password
On Thu, Jul 31, 2008 at 1:08 PM, Rafael Domiciano
<rafael.domiciano@gmail.com> wrote:
> Hi there,
>
> I have a situation that is a bit strange.
> I discovered today that I can login in programs like EMS SQL Manager and in
> the internal software (via ODBC) without using the password for the user.
>
> Example:
> Configuration on the ODBC:
> datasource: postgres
> database: Postgres
> server: "IP"
> user: 123456
> password: 123456
>
> The software connect to the ODBC getting the configs of the data source.
> And when I clear the password in the ODBC the software continues to work,
> without a passwd error.
> I certified with the programers, and the software doesn't pass the password.
<rafael.domiciano@gmail.com> wrote:
> Hi there,
>
> I have a situation that is a bit strange.
> I discovered today that I can login in programs like EMS SQL Manager and in
> the internal software (via ODBC) without using the password for the user.
>
> Example:
> Configuration on the ODBC:
> datasource: postgres
> database: Postgres
> server: "IP"
> user: 123456
> password: 123456
>
> The software connect to the ODBC getting the configs of the data source.
> And when I clear the password in the ODBC the software continues to work,
> without a passwd error.
> I certified with the programers, and the software doesn't pass the password.
You're likely logging in from the same machine and pg_hba.conf is set
to ident authentication.
Change your pg_hba.conf back to ident or trust, and run this command:
alter user postgres set password='password';
changing 'password' of course
Then change pg_hba.conf to md5 authentication.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Re: [GENERAL] eliminating records not in (select id ... so SLOW?
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.
> 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 the per-row
overhead is. Experiment until EXPLAIN tells you it'll use a hashed
subplan.
BTW, don't bother with creating the index, it doesn't help for this.
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-es-ayuda] sobre savepoints
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?
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?
Alvaro Herrera escribió:
> Linos escribió:
>
>> begin ----- savepoint ---- insert ---- releasesavepoint ---- savepoint
>> ---- insert ----- releasesavepoint ---- savepoint --- insert(error) ---
>> rollback to savepoint --- update --- releasesavepoint --- 20000 registros
>> mas ---- commit
>>
>> Lo cual a traves de una linea lenta ralentiza considerablemente. Supongo
>> que debe haber alguna manera mejor de hacer esto que evidentemente yo no
>> conozco, se puede desactivar este modo para que la transaccion siga con
>> un error hasta que yo haga un rollback especifico?
>
> No. Todos los errores dejan la transaccion en estado "abortado", del
> cual solo puedes salir abortandola completamente o bien con ROLLBACK TO
> savepoint.
>
> (Efectivamente lo que el codigo hace es un "rollback automatico"
> internamente)
>
>> otra manera que habia pensado es hacer una funcion para los inserts (q
>> cambiara a update si existe el registro) pero las columnas cambian no
>> solamente para cada tabla si no a veces en cada insert, se generan
>> con unos triggers automaticamente y no se si se podria hacer o incluso
>> si interesaria hacerlo. Me podrian echar un cable con esto? gracias.
>
> Si el problema es la cantidad extra de ordenes que tienes que enviar
> desde el cliente, creo que seria una ganancia enorme hacerlo en una
> funcion del lado del servidor, usando bloques EXCEPTION. Ahora, tienes
> que tener en cuenta que EXCEPTION crea un nuevo savepoint cada vez, asi
> que si la ralentizacion viene por el lado de crear los savepoints (lo
> cual no seria raro, porque los savepoints son relativamente lentos)
> entonces va a seguir siendo un cuello de botella.
>
> Lo otro que podrias hacer, quizas, es intentar con PGLoader.
>
--
TIP 4: No hagas 'kill -9' a postmaster
Re: [PERFORM] SSD Performance Article
On Thu, Jul 31, 2008 at 11:45 AM, Matthew T. O'Connor <matthew@zeut.net> wrote:
> Interesting read...
>
> http://www.linux.com/feature/142658
> Interesting read...
>
> http://www.linux.com/feature/142658
Wish he had used a dataset larger than 1G...
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[pgsql-de-allgemein] In Funktion prüfen ob Zeile existiert
Hallo,
ich habe hier einen teil einer Postgresfunktion welche eine Tabelle updated.
...
LOOP
-- RAISE NOTICE 'notify_id %', asset_notify.notify_id;
-- RAISE NOTICE 'asset_id %', NEW.asset_id;
-- RAISE NOTICE 'label_id %', asset_notify.label_id;
INSERT INTO asset.asset_labels (label_id, asset_id, state, login_id) values (asset_notify.label_id, NEW.asset_id, -2, asset_notify.login_id);
END LOOP;
...
Das problem welches ich gerade habe ist folgendes.
Es kann sein das mit das der insert auf die tabelle fehlschlägt weil
dort schon etwas drinsteht (unique auf asset_id, label_id). Das würde
ich gerne abfangen, das heisst wenn der insert fehlschlägt soll er ein
update machen.
Leider kenne ich mit Funktionen in Postgres nicht so gut aus, vielleicht
hilft mir ja hier jemand auf die sprünge...
Ich stelle mir einfach eine IF abfrage vor:
IF insert schlägt fehl THEN update ...
Danke schonmal,
Oli
--
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
[GENERAL]
Florence Cousin
02 51 78 38 46
fcousin@sopragroup.com
02 51 78 38 46
fcousin@sopragroup.com
-------------------------------------------------------------------------
Ce qui se conçoit bien s'énonce clairement. Et ce qui va sans dire va mieux en le disant. Anonyme (inspiré par Boileau)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [PERFORM] how to fix problem then when two queries run at the same time, it takes longer to complete then if run in sequence
Miernik wrote:
> Theo Kramer <theo@flame.co.za> wrote:
>> file `which psql`
>> /usr/bin/psql: ELF 32-bit LSB executable, Intel 80386, version 1
>> (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.9,
>> stripped
>
> miernik@tarnica:~$ file `which psql`
> /usr/bin/psql: symbolic link to `../share/postgresql-common/pg_wrapper'
> miernik@tarnica:~$ file /usr/share/postgresql-common/pg_wrapper
> /usr/share/postgresql-common/pg_wrapper: a /usr/bin/perl -w script text executable
> Theo Kramer <theo@flame.co.za> wrote:
>> file `which psql`
>> /usr/bin/psql: ELF 32-bit LSB executable, Intel 80386, version 1
>> (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.9,
>> stripped
>
> miernik@tarnica:~$ file `which psql`
> /usr/bin/psql: symbolic link to `../share/postgresql-common/pg_wrapper'
> miernik@tarnica:~$ file /usr/share/postgresql-common/pg_wrapper
> /usr/share/postgresql-common/pg_wrapper: a /usr/bin/perl -w script text executable
That's not psql though, that's Debian's wrapper around it which lets you
install multiple versions of PostgreSQL on the same machine. Might be
worth bypassing it and calling it directly.
--
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
Re: [BUGS] BUG #4336: Strange behaviour for to_ascii
Hello
it's bug,
for this moment, please, try:
CREATE FUNCTION to_ascii(bytea, name)
RETURNS text AS 'to_ascii_encname' LANGUAGE internal;
postgres=# CREATE FUNCTION to_ascii(bytea, name)
postgres-# RETURNS text AS 'to_ascii_encname' LANGUAGE internal;
CREATE FUNCTION
postgres=#
postgres=# SELECT to_ascii(convert_to('Příliš žlutý kůň', 'latin2'),'latin2');
to_ascii
------------------
Prilis zluty kun
(1 row)
postgres=# SELECT
to_ascii(convert_to('école','LATIN9'),'LATIN9');
to_ascii
----------
ecole
(1 row)
Regards
Pavel Stehule
2008/7/30 Maxime Carbonneau <manitou@maikan.com>:
>
> The following bug has been logged online:
>
> Bug reference: 4336
> Logged by: Maxime Carbonneau
> Email address: manitou@maikan.com
> PostgreSQL version: 8.3.3
> Operating system: Mac OS X 10.5.4
> Description: Strange behaviour for to_ascii
> Details:
>
> With PostgreSQL 8.2.5, I used to_ascii to remove accents:
> SELECT TO_ASCII(CONVERT('école' USING utf8_to_iso_8859_15), 'latin9');
> => ecole
> With PostgreSQL 8.3.3, I had to made some changes since convert using is
> gone. I'm using: SELECT
> to_ascii(encode(convert_to('école','LATIN9'),'escape'),'LATIN9');
> but the result is
> => \351cole
>
> How can I remove accent letter with PostgreSQL 8.3.3?
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[GENERAL] Weird pg_ctl behaviour via ssh
Hello,
I am fiddling around with pgpool-II and online recovery. Recovery depends
on remote starting of a cluster. This means I need to ssh into a box,
start clustern (with PITR recovery) and terminate that ssh connection.
If I use the following script:
ssh -T remote "export LD_LIBRARY_PATH=/opt/postgres-8.3.3/lib;
nohup /opt/postgres-8.3.3/bin/pg_ctl -w -D /data/pg833-data start >
/dev/null 2>&1;exit;"
the script terminates earlier than the DB is up:
/opt/postgres-8.3.3/bin/psql -h remote -p 5555 postgres
psql: FATAL: the database system is starting up
which is problem for pgpool. But if I use command:
ssh -T remote "export LD_LIBRARY_PATH=/opt/postgres-8.3.3/lib;
nohup /opt/postgres-8.3.3/bin/pg_ctl -w -D /data/pg833-data start
2>&1;exit;"
the ssh never terminates. Which is, again problem for pg_pool. The outoput
will be as bellow. How can I terminate the script really at the moment
when DB is up?
Thank you,
Bohdan
...
.FATAL: the database system is starting up
.scp: /data/archive_log/00000004.history: No such file or directory
could not start server
scp: /data/archive_log/00000005.history: No such file or directory
scp: /data/archive_log/00000006.history: No such file or directory
scp: /data/archive_log/00000007.history: No such file or directory
scp: /data/archive_log/00000008.history: No such file or directory
scp: /data/archive_log/00000009.history: No such file or directory
scp: /data/archive_log/0000000A.history: No such file or directory
scp: /data/archive_log/0000000B.history: No such file or directory
scp: /data/archive_log/0000000C.history: No such file or directory
scp: /data/archive_log/0000000D.history: No such file or directory
scp: /data/archive_log/0000000E.history: No such file or directory
scp: /data/archive_log/0000000F.history: No such file or directory
scp: /data/archive_log/00000010.history: No such file or directory
scp: /data/archive_log/00000011.history: No such file or directory
LOG: selected new timeline ID: 17
scp: /data/archive_log/00000001.history: No such file or directory
LOG: archive recovery complete
LOG: autovacuum launcher started
LOG: database system is ready to accept connections
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [PERFORM] how to fix problem then when two queries run at the same time, it takes longer to complete then if run in sequence
Miernik wrote:
> Richard Huxton <dev@archonet.com> wrote:
>>> I just installed pgpool2 and whoaaa! Everything its like about 3 times
>>> faster! My application are bash scripts using psql -c "UPDATE ...".
>> Probably spending most of their time setting up a new connection, then
>> clearing it down again.
>
> If I do it in Python it could do all queries in the same connection, so
> should be faster? Besides that 'psql' is written in perl, so its also
> heavy, by not using psql I get rid of perl library in RAM.
> Richard Huxton <dev@archonet.com> wrote:
>>> I just installed pgpool2 and whoaaa! Everything its like about 3 times
>>> faster! My application are bash scripts using psql -c "UPDATE ...".
>> Probably spending most of their time setting up a new connection, then
>> clearing it down again.
>
> If I do it in Python it could do all queries in the same connection, so
> should be faster? Besides that 'psql' is written in perl, so its also
> heavy, by not using psql I get rid of perl library in RAM.
Nope - "C" all through.
> Also the
> script uses wget to poll some external data sources a lot, also
> needlessly opening new connection to the webserver, so I want to make
> the script save the http connection, which means I must get rid of wget.
> Maybe I should write some parts in C?
>
> BTW, doesn't there exist any tool does what "psql -c" does, but is
> written in plain C, not perl? I was looking for such psql replacement,
> but couldn't find any
Well ECPG lets you embed SQL directly in your "C".
>>> # Number of connection pools allowed for a child process
>>> max_pool = 1
>> Might need to increase that to 2 or 3.
>
> Why? The website says:
>
> max_pool
>
> The maximum number of cached connections in pgpool-II children
> processes. pgpool-II reuses the cached connection if an incoming
> connection is connecting to the same database by the same username.
>
> But all my connections are to the same database and the same username,
> and I only ever want my application to do 1 connection to the database
> at a time, so why would I want/need 2 or 3 in max_pool?
From the subject line of your question: "how to fix problem then when
two queries run at the same time..."
Of course if you don't actually want to run two simultaneous queries,
then max_pool=1 is what you want.
>> Not well known enough on the Debian side of the fence? It's simple
>> enough to install from source though. Takes about one minute.
>
> But is there any advantage for me compared to pgpool2, which works
> really nice?
Can't say. Given your limited RAM, it's probably worth looking at both
and seeing which leaves you more memory. Your main concern has got to be
to reduce wasted RAM.
> In some parts, like doing some count(*) stuff, it now does
> things in about one second, which took a few minutes to finish before (if
> the other part of the scripts where doing something else on the database
> at the same time).
That will be because you're only running one query, I'd have thought.
Two queries might be sending you into swap.
--
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
Re: [PERFORM] how to fix problem then when two queries run at the same time, it takes longer to complete then if run in sequence
Theo Kramer <theo@flame.co.za> wrote:
> file `which psql`
> /usr/bin/psql: ELF 32-bit LSB executable, Intel 80386, version 1
> (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.9,
> stripped
> file `which psql`
> /usr/bin/psql: ELF 32-bit LSB executable, Intel 80386, version 1
> (SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.9,
> stripped
miernik@tarnica:~$ file `which psql`
/usr/bin/psql: symbolic link to `../share/postgresql-common/pg_wrapper'
miernik@tarnica:~$ file /usr/share/postgresql-common/pg_wrapper
/usr/share/postgresql-common/pg_wrapper: a /usr/bin/perl -w script text executable
miernik@tarnica:~$
--
Miernik
http://miernik.name/
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [GENERAL] Statistics Data archiving with Postgres
Pascal Cohen wrote:
> But to sum up we would like to collect statistics (write mostly tables,
> high volume generation, data not critical) on an application usage on a
> read mostly DB with the least impact on this DB perfs. ANn we would also
> like to be able to archive outside the DB, the old collected data.
[Just throwing ideas around - there's probably a much better and already
well established to do all this]:
Does the stats data need to be handled within the same transactional
scope as the real app data? In other words, do you care if a stats entry
is generated for a transaction that then rolls back?
If you're OK with that, then another possible area to investigate is the
use of IPC messaging to a separate stats collector daemon outside of,
and running asynchronously with, PostgreSQL. Many of the PostgreSQL
procedural languages can communicate outside the server with mechanisms
like UNIX sockets, IP networking, signals, named pipes, and perhaps even
things like shared memory if the Pg backend's use of it doesn't
interfere. Alternately, you could write your stats collector client as a
C add-in to Pg, which would probably let you minimize it's performance
cost in exchange for more development and debugging time, plus a higher
risk to server stability.
Ideally the part of the logging/stats code running inside the Pg backend
would do as little work as possible to record the message for later
processing. The external stats collector/processor would asynchronously
process messages it receives from all Pg backends and record it in your
preferred format (perhaps inserting it into a separate write-optimised
Pg database on another host).
Come to think of it, having stats recorded for transactions that roll
back is probably desirable, rather than any sort of downside.
--
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: [PERFORM] how to fix problem then when two queries run at the same time, it takes longer to complete then if run in sequence
On 31 Jul 2008, at 10:29AM, Miernik wrote:
> Richard Huxton <dev@archonet.com> wrote:
>>> I just installed pgpool2 and whoaaa! Everything its like about 3
>>> times
>>> faster! My application are bash scripts using psql -c "UPDATE ...".
>>
>> Probably spending most of their time setting up a new connection,
>> then
>> clearing it down again.
>
> If I do it in Python it could do all queries in the same connection,
> so
> should be faster? Besides that 'psql' is written in perl, so its also
> heavy, by not using psql I get rid of perl library in RAM. Also the
> script uses wget to poll some external data sources a lot, also
> needlessly opening new connection to the webserver, so I want to make
> the script save the http connection, which means I must get rid of
> wget.
> Maybe I should write some parts in C?
>
> BTW, doesn't there exist any tool does what "psql -c" does, but is
> written in plain C, not perl? I was looking for such psql replacement,
> but couldn't find any.
?
file `which psql`
/usr/bin/psql: ELF 32-bit LSB executable, Intel 80386, version 1
(SYSV), dynamically linked (uses shared libs), for GNU/Linux 2.6.9,
stripped
--
Regards
Theo
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [HACKERS] Should creating a new base type require superuser status?
On Wed, Jul 30, 2008 at 06:07:53PM -0400, Alvaro Herrera wrote:
> I do agree that creating base types should require a superuser though.
> It too seems dangerous just on principle, even if today there's no
> actual hole (that we already know of).
I agree.
--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [PERFORM] how to fix problem then when two queries run at the same time, it takes longer to complete then if run in sequence
Richard Huxton <dev@archonet.com> wrote:
>> I just installed pgpool2 and whoaaa! Everything its like about 3 times
>> faster! My application are bash scripts using psql -c "UPDATE ...".
>
> Probably spending most of their time setting up a new connection, then
> clearing it down again.
>> I just installed pgpool2 and whoaaa! Everything its like about 3 times
>> faster! My application are bash scripts using psql -c "UPDATE ...".
>
> Probably spending most of their time setting up a new connection, then
> clearing it down again.
If I do it in Python it could do all queries in the same connection, so
should be faster? Besides that 'psql' is written in perl, so its also
heavy, by not using psql I get rid of perl library in RAM. Also the
script uses wget to poll some external data sources a lot, also
needlessly opening new connection to the webserver, so I want to make
the script save the http connection, which means I must get rid of wget.
Maybe I should write some parts in C?
BTW, doesn't there exist any tool does what "psql -c" does, but is
written in plain C, not perl? I was looking for such psql replacement,
but couldn't find any.
>> # Number of connection pools allowed for a child process
>> max_pool = 1
>
> Might need to increase that to 2 or 3.
Why? The website says:
max_pool
The maximum number of cached connections in pgpool-II children
processes. pgpool-II reuses the cached connection if an incoming
connection is connecting to the same database by the same username.
But all my connections are to the same database and the same username,
and I only ever want my application to do 1 connection to the database
at a time, so why would I want/need 2 or 3 in max_pool?
> Not well known enough on the Debian side of the fence? It's simple
> enough to install from source though. Takes about one minute.
But is there any advantage for me compared to pgpool2, which works
really nice? In some parts, like doing some count(*) stuff, it now does
things in about one second, which took a few minutes to finish before (if
the other part of the scripts where doing something else on the database
at the same time).
--
Miernik
http://miernik.name/
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[GENERAL] Statistics Data archiving with Postgres
Hello
We are developing an application and would like to compute statistics on
it in order:
- to have a better understanding of what is used mostly in our
application to model at best our load test scenarios.
- to get information on the usage of the application for other departments.
We are developing an application and would like to compute statistics on
it in order:
- to have a better understanding of what is used mostly in our
application to model at best our load test scenarios.
- to get information on the usage of the application for other departments.
The problem is that our application is currently read mostly while
statistics logging is more a log write mostly process. And stats collect
will generate a huge volume of data (because a very low granularity is
mandatory).
We would like to avoid as much as possible any interference of the stats
collecting with the main application.
We have looked in the Postgres documentation and we have found several
ideas:
- We have decided to isolate stats in a specific schema.
- We have looked at polymorphism in order to split our stat tables in
smallest ones that we could "detach" when they are old.
- We have looked at fsync tuning or better at asynchronous commit as
these data are not critical.
But we have been facing several questions/problems:
Polymorphism and ORM question:
- First as we are using an ORM tool around PG access, the rule we
defined in the Polymorphism returned 0 after an insert because the last
rule was generally not the one that made the insert. In our case we know
that only a single rule will match, so we made a hack setting the active
rule name with a zzz but that is very hacky. in that case anyway
Hibernate is happy.
One or several databases, one or several servers ?
- In such a case could we store both our application content and stats
in the same database ? Should we better use two databases in the same
cluster or should we even have to different dedicated servers ?
- If we want to use fsync, I suppose we need two separated servers. I
read that asynchronous commit can be set for a transaction. Is there a
way to say that a given cluster or tables are in asynchronous commit by
default, perhaps with triggers ....
We would like to archive old data collected in slow file storage in any
case but would like to avoid having our database reaching Tb only for
data collecting concerns. May be this is a bad idea. Anyway if this is
not so bad, we have again questions:
With polymorphism we can dump some tables regularly. But polymorphism
has been seen a bit complex and we were studying a simpler way to and we
also have to study other ways with simpler but larger stats tables.
We have studied the simple pg_dump command with only the data but we
would need to dump only a part of the table. Thus we have looked at the
COPY command which seems interesting in our case. Are there experience
or any feedback on that command.
Sorry, there are many questions, our problem is a bit wide because there
are several concerns:
- Polymorphism or not
- One or several DB clusters or servers
- Fsync/asynchronous problem
- Rule limitations
- Use of COPY
But to sum up we would like to collect statistics (write mostly tables,
high volume generation, data not critical) on an application usage on a
read mostly DB with the least impact on this DB perfs. ANn we would also
like to be able to archive outside the DB, the old collected data.
Thanks for any help!
Pascal
--
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] Clone a database to other machine
Garg, Manjit wrote:
> Hi ,
>
> Actually I was looking for some method / tool (utility) which keeps both
> the database on different servers in sync automatically.
>
> Looking for some features into postgres.conf file if possible.
> Hi ,
>
> Actually I was looking for some method / tool (utility) which keeps both
> the database on different servers in sync automatically.
>
> Looking for some features into postgres.conf file if possible.
There's no built in replication for postgres at the moment.
Check out slony (http://slony.info/) - it's a master->multiple slave
replication system and seems to work pretty well.
--
Postgresql & php tutorials
http://www.designmagick.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] Type Categories for User-Defined Types
Hi,
Le mercredi 30 juillet 2008, David E. Wheeler a écrit :
> On Jul 30, 2008, at 10:34, Tom Lane wrote:
> >>> [ move preferred-type info into the system catalogs ]
> > UUID and so on aren't considered part of the string category, and
> > shouldn't be IMHO ... any type that has semantics significantly
> > different from "arbitrary string of text" doesn't belong.
>
> Yes, that was essentially my point. "arbitrary string of text" types
> are probably fairly rare, since one can just use text or citext or
> varchar.
I'm not following this thread closely (enough) but my prefix_range type (from
the pgfoundry prefix module) certainly is a user defined (in C) "arbitrary
string of text" type. The fact that it's user visible could be questionned,
its usage is for GiST indexing prefix searches when the prefix is in the
table, not in the query literal:
SELECT * FROM prefixes WHERE prefix @> 'literal';
This query currently only profits from the GiST indexing if prefix column is
of type prefix_range, I'd like to be able to index text and store
prefix_range in the index, it's on the TODO list (and certainly is doable
AFAIUI).
I'm not sure this input is valuable for the topic, but would rather not miss
the example in case it is :)
> > The behavior that's hard-wired into parse_coerce.c at the moment
> > is that only text, varchar, bpchar can be sources or targets of
> > I/O conversions. While opening it up to citext sounds reasonable,
> > I'm a lot less sure about domains.
I still remember the sour taste of cancelling a domain usage for some type
needs here after discovering you can't create CAST for domains. Would the
categorising stuff help here?
Regards,
--
dim
Re: [PERFORM] how to fix problem then when two queries run at the same time, it takes longer to complete then if run in sequence
Miernik wrote:
>> Might be worth turning off autovacuum and running a manual vacuum full
>> overnight if your database is mostly reads.
>
> I run autovacum, and the database has a lot of updates all the time,
> also TRUNCATING tables and refilling them, usually one or two
> INSERTS/UPDATES per second.
>> Might be worth turning off autovacuum and running a manual vacuum full
>> overnight if your database is mostly reads.
>
> I run autovacum, and the database has a lot of updates all the time,
> also TRUNCATING tables and refilling them, usually one or two
> INSERTS/UPDATES per second.
OK
>> Finally, I think it's worth looking at pgpool or pgbouncer (as Alvaro
>> said) and set them to allow only one connection in the pool. I know
>> that pgbouncer offers per-transaction connection sharing which will
>> make this more practical. Even so, it will help if your application
>> can co-operate by closing the connection as soon as possible.
>
> I just installed pgpool2 and whoaaa! Everything its like about 3 times
> faster! My application are bash scripts using psql -c "UPDATE ...".
Probably spending most of their time setting up a new connection, then
clearing it down again.
> I plan to rewrite it in Python, not sure if it would improve
> performance, but will at least be a "cleaner" implementation.
Careful of introducing any more overheads though. If libraries end up
using another 2.5MB of RAM then that's 10% of your disk-cache gone.
> In /etc/pgpool.conf I used:
>
> # number of pre-forked child process
> num_init_children = 1
>
> # Number of connection pools allowed for a child process
> max_pool = 1
Might need to increase that to 2 or 3.
> Wanted to install pgbouncer, but it is broken currently in Debian. And
> why is it in contrib and not in main (speaking of Debian location)?
Not well known enough on the Debian side of the fence? It's simple
enough to install from source though. Takes about one minute.
--
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
Re: [ADMIN] pgAdmin III
On Wed, Jul 30, 2008 at 09:15:38PM -0500, Jim Toth wrote:
> I've installed PostgreSql 8.3 on Ubuntu 8.04. When I click on the pgAdmin III
> link, nothing happens. I'm a newbie here, any help would be appreciated...
> I've installed PostgreSql 8.3 on Ubuntu 8.04. When I click on the pgAdmin III
> link, nothing happens. I'm a newbie here, any help would be appreciated...
Did you install pgadmin3 and pgadmin3-data also?
Regards
Johann
--
Johann Spies Telefoon: 021-808 4036
Informasietegnologie, Universiteit van Stellenbosch
"Have not I commanded thee? Be strong and of a good
courage; be not afraid, neither be thou dismayed: for
the LORD thy God is with thee whithersoever thou
goest." Joshua 1:9
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Re: [HACKERS] [PATCH] "\ef " in psql
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
index 1ba20b0..ccf0d68 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -551,6 +551,7 @@ extern Datum pg_get_expr(PG_FUNCTION_ARGS);
extern Datum pg_get_expr_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_userbyid(PG_FUNCTION_ARGS);
extern Datum pg_get_serial_sequence(PG_FUNCTION_ARGS);
+extern Datum pg_get_functiondef(PG_FUNCTION_ARGS);
extern Datum pg_get_function_arguments(PG_FUNCTION_ARGS);
extern Datum pg_get_function_result(PG_FUNCTION_ARGS);
extern char *deparse_expression(Node *expr, List *dpcontext,
index 1ba20b0..ccf0d68 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -551,6 +551,7 @@ extern Datum pg_get_expr(PG_FUNCTION_ARGS);
extern Datum pg_get_expr_ext(PG_FUNCTION_ARGS);
extern Datum pg_get_userbyid(PG_FUNCTION_ARGS);
extern Datum pg_get_serial_sequence(PG_FUNCTION_ARGS);
+extern Datum pg_get_functiondef(PG_FUNCTION_ARGS);
extern Datum pg_get_function_arguments(PG_FUNCTION_ARGS);
extern Datum pg_get_function_result(PG_FUNCTION_ARGS);
extern char *deparse_expression(Node *expr, List *dpcontext,
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 0d28310..71e601a 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -137,6 +137,7 @@ static char *pg_get_expr_worker(text *expr, Oid relid, char *relname,
int prettyFlags);
static int print_function_arguments(StringInfo buf, HeapTuple proctup,
bool print_table_args);
+static void print_function_rettype(StringInfo buf, HeapTuple proctup);
static void make_ruledef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc,
int prettyFlags);
static void make_viewdef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc,
@@ -1398,6 +1399,137 @@ pg_get_serial_sequence(PG_FUNCTION_ARGS)
/*
+ * pg_get_functiondef
+ * Returns the "CREATE OR REPLACE FUNCTION ..." statement for the
+ * specified function.
+ */
+Datum
+pg_get_functiondef(PG_FUNCTION_ARGS)
+{
+ Oid funcid = PG_GETARG_OID(0);
+ StringInfoData buf;
+ StringInfoData dq;
+ HeapTuple proctup;
+ HeapTuple langtup;
+ Form_pg_proc proc;
+ Form_pg_language lang;
+ bool isnull;
+ Datum tmp;
+ const char *prosrc;
+ const char *name;
+ const char *nsp;
+ float4 cost;
+ int n;
+
+ initStringInfo(&buf);
+
+ proctup = SearchSysCache(PROCOID, ObjectIdGetDatum(funcid), 0, 0, 0);
+ if (!HeapTupleIsValid(proctup))
+ elog(ERROR, "cache lookup failed for function %u", funcid);
+ proc = (Form_pg_proc) GETSTRUCT(proctup);
+
+ langtup = SearchSysCache(LANGOID, ObjectIdGetDatum(proc->prolang), 0, 0, 0);
+ if (!HeapTupleIsValid(langtup))
+ elog(ERROR, "cache lookup failed for language %u", proc->prolang);
+ lang = (Form_pg_language) GETSTRUCT(langtup);
+
+ name = NameStr(proc->proname);
+ nsp = get_namespace_name(proc->pronamespace);
+ appendStringInfo(&buf, "CREATE OR REPLACE FUNCTION %s(",
+ quote_qualified_identifier(nsp, name));
+ (void) print_function_arguments(&buf, proctup, false);
+ appendStringInfoString(&buf, ")\n RETURNS ");
+ print_function_rettype(&buf, proctup);
+ appendStringInfo(&buf, "\n LANGUAGE '%s'\n", NameStr(lang->lanname));
+
+ n = 1;
+
+ switch (proc->provolatile) {
+ case PROVOLATILE_IMMUTABLE:
+ appendStringInfoString(&buf, " IMMUTABLE");
+ break;
+ case PROVOLATILE_STABLE:
+ appendStringInfoString(&buf, " STABLE");
+ break;
+ case PROVOLATILE_VOLATILE:
+ default:
+ n--;
+ break;
+ }
+
+ if (proc->proisstrict)
+ {
+ n++;
+ appendStringInfoString(&buf, " STRICT");
+ }
+
+ if (proc->prosecdef)
+ {
+ n++;
+ appendStringInfoString(&buf, " SECURITY DEFINER");
+ }
+
+ cost = 100;
+ if (proc->prolang == INTERNALlanguageId ||
+ proc->prolang == ClanguageId)
+ cost = 1;
+
+ if (proc->procost != cost)
+ {
+ n++;
+ appendStringInfo(&buf, " COST %.0f", proc->procost);
+ }
+
+ if (proc->prorows != 0 && proc->prorows != 1000)
+ {
+ n++;
+ appendStringInfo(&buf, " ROWS %.0f", proc->prorows);
+ }
+
+ if (n != 0)
+ appendStringInfoString(&buf, "\n");
+
+ tmp = SysCacheGetAttr(PROCOID, proctup, Anum_pg_proc_proconfig, &isnull);
+ if (!isnull)
+ {
+ int i;
+ ArrayType *a = DatumGetArrayTypeP(tmp);
+
+ for (i = 1; i <= ARR_DIMS(a)[0]; i++)
+ {
+ Datum d;
+ bool isnull;
+
+ d = array_ref(a, 1, &i, -1, -1, false, 'i', &isnull);
+ if (!isnull)
+ {
+ const char *s = TextDatumGetCString(d);
+ appendStringInfo(&buf, " SET %s\n", s);
+ }
+ }
+ }
+
+ tmp = SysCacheGetAttr(PROCOID, proctup, Anum_pg_proc_prosrc, &isnull);
+ if (isnull)
+ elog(ERROR, "null prosrc");
+ prosrc = TextDatumGetCString(tmp);
+
+ initStringInfo(&dq);
+ appendStringInfoString(&dq, "$");
+ while (strstr(prosrc, dq.data) != NULL)
+ appendStringInfoString(&dq, "x");
+ appendStringInfoString(&dq, "$");
+
+ appendStringInfo(&buf, "AS %s\n%s\n%s;", dq.data, prosrc, dq.data);
+
+ ReleaseSysCache(langtup);
+ ReleaseSysCache(proctup);
+
+ PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
+
+
+/*
* pg_get_function_arguments
* Get a nicely-formatted list of arguments for a function.
* This is everything that would go between the parentheses in
@@ -1436,8 +1568,6 @@ pg_get_function_result(PG_FUNCTION_ARGS)
Oid funcid = PG_GETARG_OID(0);
StringInfoData buf;
HeapTuple proctup;
- Form_pg_proc procform;
- int ntabargs = 0;
initStringInfo(&buf);
@@ -1446,32 +1576,46 @@ pg_get_function_result(PG_FUNCTION_ARGS)
0, 0, 0);
if (!HeapTupleIsValid(proctup))
elog(ERROR, "cache lookup failed for function %u", funcid);
- procform = (Form_pg_proc) GETSTRUCT(proctup);
+ print_function_rettype(&buf, proctup);
+ ReleaseSysCache(proctup);
+
+ PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
+
+
+/* Appends a function's return type to the specified buffer. */
- if (procform->proretset)
+void print_function_rettype(StringInfo buf, HeapTuple proctup)
+{
+ int ntabargs = 0;
+ Form_pg_proc proc = (Form_pg_proc) GETSTRUCT(proctup);
+ StringInfoData b;
+
+ initStringInfo(&b);
+
+ if (proc->proretset)
{
/* It might be a table function; try to print the arguments */
- appendStringInfoString(&buf, "TABLE(");
- ntabargs = print_function_arguments(&buf, proctup, true);
+ appendStringInfoString(&b, "TABLE(");
+ ntabargs = print_function_arguments(&b, proctup, true);
if (ntabargs > 0)
- appendStringInfoString(&buf, ")");
+ appendStringInfoString(&b, ")");
else
- resetStringInfo(&buf);
+ resetStringInfo(&b);
}
if (ntabargs == 0)
{
/* Not a table function, so do the normal thing */
- if (procform->proretset)
- appendStringInfoString(&buf, "SETOF ");
- appendStringInfoString(&buf, format_type_be(procform->prorettype));
+ if (proc->proretset)
+ appendStringInfoString(&b, "SETOF ");
+ appendStringInfoString(&b, format_type_be(proc->prorettype));
}
- ReleaseSysCache(proctup);
-
- PG_RETURN_TEXT_P(string_to_text(buf.data));
+ appendStringInfoString(buf, b.data);
}
+
/*
* Common code for pg_get_function_arguments and pg_get_function_result:
* append the desired subset of arguments to buf. We print only TABLE
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 16ccb55..0f65534 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -2291,6 +2291,8 @@ DATA(insert OID = 1716 ( pg_get_expr PGNSP PGUID 12 1 0 0 f f t f s 2 25 "2
DESCR("deparse an encoded expression");
DATA(insert OID = 1665 ( pg_get_serial_sequence PGNSP PGUID 12 1 0 0 f f t f s 2 25 "25 25" _null_ _null_ _null_ pg_get_serial_sequence _null_ _null_ _null_ ));
DESCR("name of sequence for a serial column");
+DATA(insert OID = 2176 ( pg_get_functiondef PGNSP PGUID 12 1 0 0 f f t f s 1 25 "26" _null_ _null_ _null_ pg_get_functiondef _null_ _null_ _null_ ));
+DESCR("definition of a function");
DATA(insert OID = 2162 ( pg_get_function_arguments PGNSP PGUID 12 1 0 0 f f t f s 1 25 "26" _null_ _null_ _null_ pg_get_function_arguments _null_ _null_ _null_ ));
DESCR("argument list of a function");
DATA(insert OID = 2165 ( pg_get_function_result PGNSP PGUID 12 1 0 0 f f t f s 1 25 "26" _null_ _null_ _null_ pg_get_function_result _null_ _null_ _null_ ));
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 448a302..b0b7438 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -11563,6 +11563,10 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
</indexterm>
<indexterm>
+ <primary>pg_get_functiondef</primary>
+ </indexterm>
+
+ <indexterm>
<primary>pg_get_function_arguments</primary>
</indexterm>
@@ -11644,6 +11648,11 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
in it refer to the relation indicated by the second parameter</entry>
</row>
<row>
+ <entry><literal><function>pg_get_functiondef</function>(<parameter>func_oid</parameter>)</literal></entry>
+ <entry><type>text</type></entry>
+ <entry>get definition of a function</entry>
+ </row>
+ <row>
<entry><literal><function>pg_get_function_arguments</function>(<parameter>func_oid</parameter>)</literal></entry>
<entry><type>text</type></entry>
<entry>get argument list for function</entry>
@@ -11756,6 +11765,8 @@ SELECT pg_type_is_visible('myschema.widget'::regtype);
</para>
<para>
+ <function>pg_get_functiondef</> returns the <command>CREATE FUNCTION</>
+ statement for a function.
<function>pg_get_function_arguments</function> returns the argument list
of a function, in the form it would need to appear in within
<command>CREATE FUNCTION</>.
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 9e6923f..fd61034 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -56,9 +56,12 @@
static backslashResult exec_command(const char *cmd,
PsqlScanState scan_state,
PQExpBuffer query_buf);
-static bool do_edit(const char *filename_arg, PQExpBuffer query_buf);
+static bool do_edit(const char *filename_arg, PQExpBuffer query_buf,
+ bool *edited);
static bool do_connect(char *dbname, char *user, char *host, char *port);
static bool do_shell(const char *command);
+static bool lookup_function_oid(PGconn *conn, const char *desc, Oid *result);
+static const char *create_or_replace_function_text(PGconn *conn, Oid oid);
#ifdef USE_SSL
static void printSSLInfo(void);
@@ -444,11 +447,76 @@ exec_command(const char *cmd,
expand_tilde(&fname);
if (fname)
canonicalize_path(fname);
- status = do_edit(fname, query_buf) ? PSQL_CMD_NEWEDIT : PSQL_CMD_ERROR;
+ if (do_edit(fname, query_buf, NULL))
+ status = PSQL_CMD_NEWEDIT;
+ else
+ status = PSQL_CMD_ERROR;
free(fname);
}
}
+ /*
+ * \ef -- edit the named function in $EDITOR.
+ */
+
+ else if (strcmp(cmd, "ef") == 0)
+ {
+ Oid foid;
+ char *func;
+
+ func = psql_scan_slash_option(scan_state, OT_WHOLE_LINE, NULL, true);
+ if (!func)
+ {
+ psql_error("no function name specified\n");
+ status = PSQL_CMD_ERROR;
+ }
+ else if (!lookup_function_oid(pset.db, func, &foid))
+ {
+ psql_error(PQerrorMessage(pset.db));
+ status = PSQL_CMD_ERROR;
+ }
+ else {
+ termPQExpBuffer(query_buf);
+ if (foid)
+ {
+ char *s = create_or_replace_function_text(pset.db, foid);
+ if (s)
+ {
+ appendPQExpBufferStr(query_buf, s);
+ free(s);
+ }
+ else
+ status = PSQL_CMD_ERROR;
+ }
+ else
+ {
+ printfPQExpBuffer(query_buf,
+ "CREATE FUNCTION %s%s RETURNS ... AS $$\n"
+ "...\n"
+ "$$ LANGUAGE '...'\n",
+ func, strchr(func,'(') ? "" : "(...)" );
+ }
+ }
+
+ if (status != PSQL_CMD_ERROR)
+ {
+ bool edited = false;
+ if (!do_edit(0, query_buf, &edited))
+ {
+ status = PSQL_CMD_ERROR;
+ }
+ else if (!edited)
+ {
+ printf("No changes\n");
+ }
+ else
+ {
+ status = PSQL_CMD_SEND;
+ }
+ free(func);
+ }
+ }
+
/* \echo and \qecho */
else if (strcmp(cmd, "echo") == 0 || strcmp(cmd, "qecho") == 0)
{
@@ -1410,7 +1478,7 @@ editFile(const char *fname)
/* call this one */
static bool
-do_edit(const char *filename_arg, PQExpBuffer query_buf)
+do_edit(const char *filename_arg, PQExpBuffer query_buf, bool *edited)
{
char fnametmp[MAXPGPATH];
FILE *stream = NULL;
@@ -1532,6 +1600,10 @@ do_edit(const char *filename_arg, PQExpBuffer query_buf)
psql_error("%s: %s\n", fname, strerror(errno));
error = true;
}
+ else if (edited)
+ {
+ *edited = true;
+ }
fclose(stream);
}
@@ -1912,3 +1984,66 @@ do_shell(const char *command)
}
return true;
}
+
+/*
+ * This function takes a function description, e.g. "x" or "x(int)", and
+ * issues a query on the given connection to retrieve the function's oid
+ * using a cast to regproc or regprocedure (as appropriate). The result,
+ * if there is one, is stored in the integer pointed to by result, which
+ * is assumed to be non-zero. If there are no results (i.e. the function
+ * does not exist), 0 is stored. The function then returns true.
+ *
+ * If the oid lookup query fails (which it will, for example, when
+ * multiple functions match the given description), it returns false.
+ */
+
+static bool
+lookup_function_oid(PGconn *conn, const char *desc, Oid *result)
+{
+ PGresult *res;
+ PQExpBuffer buf;
+
+ buf = createPQExpBuffer();
+ printfPQExpBuffer(buf, "SELECT '%s'::%s::oid",
+ desc, strchr(desc, '(') ? "regprocedure" : "regproc");
+
+ res = PQexec(conn, buf->data);
+ if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
+ return false;
+
+ *result = 0;
+ if (PQntuples(res) > 0)
+ *result = atooid(PQgetvalue(res, 0, 0));
+
+ destroyPQExpBuffer(buf);
+ PQclear(res);
+
+ return true;
+}
+
+/*
+ * Returns the "CREATE OR REPLACE FUNCTION ..." statement that was used
+ * to create the function with the given oid, which is assumed to be the
+ * result of lookup_function_oid() (i.e. a valid oid from pg_proc).
+ */
+
+static const char *
+create_or_replace_function_text(PGconn *conn, Oid oid)
+{
+ PGresult *res;
+ PQExpBuffer buf;
+ const char *s = 0;
+
+ buf = createPQExpBuffer();
+ printfPQExpBuffer(buf, "SELECT pg_get_functiondef(%d)", oid);
+
+ res = PQexec(conn, buf->data);
+ if (!res || PQresultStatus(res) != PGRES_TUPLES_OK || PQntuples(res) != 1)
+ return NULL;
+ s = pg_strdup(PQgetvalue(res, 0, 0));
+
+ destroyPQExpBuffer(buf);
+ PQclear(res);
+
+ return s;
+}
I have attached two patches:
- funcdef.diff implements pg_get_functiondef()
- edit.diff implements "\ef function" in psql based on (1).
Comments appreciated.
-- ams
Wednesday, July 30, 2008
Re: [HACKERS] window function v03 against HEAD
On Thu, Jul 31, 2008 at 02:46:29PM +0900, Hitoshi Harada wrote:
> 2008/7/31 David Fetter <david@fetter.org>:
> > On Thu, Jul 31, 2008 at 02:12:26PM +0900, Hitoshi Harada wrote:
> >> 2008/7/29 David Fetter <david@fetter.org>:
> >> > On Mon, Jul 28, 2008 at 07:25:55PM +0900, H.Harada wrote:
> >> >> I happily announce that the first design of window function was
> >> >> finished and the patch against HEAD is released online. See
> >> >> http://umitanuki.net/pgsql/wfv03/design.html
> >> >
> >> > I've put up a git repository at
> >> > <http://git.postgresql.org/?p=~davidfetter/window_functions/.git;a=summary>
> >>
> >> Thanks a lot.
> >> I have tried to get clone from the URL but it didn't work.
> >>
> >> $ git-clone http://git.postgresql.org/git/~davidfetter/window_functions/.git
> >> Initialized empty Git repository in /home/forcia/repo/window_functions/.git/
> >> Getting alternates list for
> >> http://git.postgresql.org/git/~davidfetter/window_functions/.git
> >> Getting pack list for
> >> http://git.postgresql.org/git/~davidfetter/window_functions/.git
> >> Getting index for pack 0c6bd5d85a8e0c5e0dee1a4e91b6c2691cb53555
> >> Getting pack 0c6bd5d85a8e0c5e0dee1a4e91b6c2691cb53555
> >> which contains c13ba377a6e58a05b5d9e39e36674af42126d48d
> >>
> >> here it stops and tells nothing. This occurs on both Linux and
> >> Windows clients.
> >
> > How long does it hang for?
> >
>
> Sorry, finally I got it. It took about an hour...
> 2008/7/31 David Fetter <david@fetter.org>:
> > On Thu, Jul 31, 2008 at 02:12:26PM +0900, Hitoshi Harada wrote:
> >> 2008/7/29 David Fetter <david@fetter.org>:
> >> > On Mon, Jul 28, 2008 at 07:25:55PM +0900, H.Harada wrote:
> >> >> I happily announce that the first design of window function was
> >> >> finished and the patch against HEAD is released online. See
> >> >> http://umitanuki.net/pgsql/wfv03/design.html
> >> >
> >> > I've put up a git repository at
> >> > <http://git.postgresql.org/?p=~davidfetter/window_functions/.git;a=summary>
> >>
> >> Thanks a lot.
> >> I have tried to get clone from the URL but it didn't work.
> >>
> >> $ git-clone http://git.postgresql.org/git/~davidfetter/window_functions/.git
> >> Initialized empty Git repository in /home/forcia/repo/window_functions/.git/
> >> Getting alternates list for
> >> http://git.postgresql.org/git/~davidfetter/window_functions/.git
> >> Getting pack list for
> >> http://git.postgresql.org/git/~davidfetter/window_functions/.git
> >> Getting index for pack 0c6bd5d85a8e0c5e0dee1a4e91b6c2691cb53555
> >> Getting pack 0c6bd5d85a8e0c5e0dee1a4e91b6c2691cb53555
> >> which contains c13ba377a6e58a05b5d9e39e36674af42126d48d
> >>
> >> here it stops and tells nothing. This occurs on both Linux and
> >> Windows clients.
> >
> > How long does it hang for?
> >
>
> Sorry, finally I got it. It took about an hour...
Sorry about that. Apparently, at least the way things are set up,
there's a *lot* of history you can rewind. Further changes should
move pretty quickly :)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [GENERAL] archive_timeout, checkpoint_timeout
On Wed, 30 Jul 2008, Rob Adams wrote:
> Could someone please explain in layman's terms the implications of using a
> checkpoint_timeout of ~1min as well? Is it a bad idea?
Lowering checkpoint_timeout makes checkpoints more frequent, causing the
database to go through WAL segments (at 16MB each) more often. Since
those get reused as needed, the peak disk usage footprint of your server
shouldn't be any higher. However, churning through that extra disk space
and doing the checkpoint bookkeeping so often can cause your server
performance to suffer a bit during heavy activity. Make sure to watch
what the server looks like under peak load, you may discover that lowering
these timeouts so much can cause it to have more trouble keeping up.
That's the usual trade-off here; the more often you want to ship useful
copies of things to another server, the more processing and particularly
disk overhead goes along with that.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [PERFORM] what is less resource-intensive, WHERE id IN or INNER JOIN?
WHERE id IN will generally lead to faster query plans. Often, much faster on large tables.
The inner join deals with duplicate values for id differently. WHERE id IN ( subquery ) will be much more likely to choose a hash method for filtering the scan on table1.
I just ran into this today on a query of similar nature on a table with 2M rows (table1 below) joining on a table with 100k (table 2). The speedup was about 10x -- 20 seconds to 2 seconds in my case -- but I'm working with many GB of RAM and 800MB of work_mem.
I'm not an expert on the query planner guts -- the below is all knowledge based on experimenting with queries on my dataset and triggering various query plans. Due to the differences in semantics of these two queries the plan on larger tables will be two sorts and a merge join for the INNER JOIN on most columns, though indexes and especially unique indexes will change this. This holds true even if the number of distinct values of the column being joined on is very low.
The WHERE id IN version will produce a much faster query plan most of the time, assuming you have enough work_mem configured. The planner is conservative if it estimates usage of work_mem to overflow even a little bit -- and often shifts to sorts rather than hashes on disk.
Since you are working with such a small ammount of RAM, make sure you have some of it doled out to work_mem and tune the balance between work_mem, the OS, and the shared buffers carefully. The conservative, sort-if-uncertain nature of the query planner may need some coersion with an unusual environment such as yours. You may even have faster results with a hash overflown to disk than a sort overflown to disk with that little memory if the % of overflow is small enough and the OS disk cache large enough. Plus, virtual machines sometimes do some odd things with caching non sync disk writes that may distort the usual random versus sequential disk cost for small I/O volumes. Though my VM experience is VMWare not Xen.
The querry planner won't generally go for hashes on disk on purpose however, so you might need to be creative with manual statistics setting or changing the optimizer cost settings to experiment with various query plans and measure the unique aspects of your atypical environment and your data.
The inner join deals with duplicate values for id differently. WHERE id IN ( subquery ) will be much more likely to choose a hash method for filtering the scan on table1.
I just ran into this today on a query of similar nature on a table with 2M rows (table1 below) joining on a table with 100k (table 2). The speedup was about 10x -- 20 seconds to 2 seconds in my case -- but I'm working with many GB of RAM and 800MB of work_mem.
I'm not an expert on the query planner guts -- the below is all knowledge based on experimenting with queries on my dataset and triggering various query plans. Due to the differences in semantics of these two queries the plan on larger tables will be two sorts and a merge join for the INNER JOIN on most columns, though indexes and especially unique indexes will change this. This holds true even if the number of distinct values of the column being joined on is very low.
The WHERE id IN version will produce a much faster query plan most of the time, assuming you have enough work_mem configured. The planner is conservative if it estimates usage of work_mem to overflow even a little bit -- and often shifts to sorts rather than hashes on disk.
Since you are working with such a small ammount of RAM, make sure you have some of it doled out to work_mem and tune the balance between work_mem, the OS, and the shared buffers carefully. The conservative, sort-if-uncertain nature of the query planner may need some coersion with an unusual environment such as yours. You may even have faster results with a hash overflown to disk than a sort overflown to disk with that little memory if the % of overflow is small enough and the OS disk cache large enough. Plus, virtual machines sometimes do some odd things with caching non sync disk writes that may distort the usual random versus sequential disk cost for small I/O volumes. Though my VM experience is VMWare not Xen.
The querry planner won't generally go for hashes on disk on purpose however, so you might need to be creative with manual statistics setting or changing the optimizer cost settings to experiment with various query plans and measure the unique aspects of your atypical environment and your data.
On Wed, Jul 30, 2008 at 3:11 PM, Miernik <public@public.miernik.name> wrote:
AFAIK, provided bar is UNIQUE in table2 (e.g. is a PRIMARY KEY) the two
queries will give the same result:
SELECT foo, id FROM table1 WHERE id IN (SELECT id FROM table2);
SELECT foo, id FROM table1 INNER JOIN table2 USING (id);
Given table1 has about 100k rows, and table2 about 100 rows, which one
should be faster, less resource intensive, use less RAM, disk access, etc?
Are there any other even better ways to acomlish the same query?
Using 8.3.3 on a 48 MB RAM Xen.
--
Miernik
http://miernik.name/
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Subscribe to:
Posts (Atom)
