Wednesday, 24 January 2007
|
| XML type in PostgreSQL 8.3 Peter Eisentraut 18:45:33 |
| | PostgreSQL 8.3 will come with a native xml type and support functions.
It would be nice if the JDBC driver could also make the appropriate
additions for handling this type. Java 6 adds the java.sql.SQLXML
interface to encapsulate values of that type, along with
ResultSet.getSQLXML and so on. Documentation is here:
http://java.sun.com/javase/6/docs/api/java/sql/SQLXML.html
The xml type in PostgreSQL CVS head should be fully functional to the
extent that one would need for developing this support. Initial
documentation is here:
http://developer.postgresql.org/pgdocs/postgres/datatype-xml.html
Maybe someone wants to tackle this, or at least make a note of it for
the future. Let me know how I can help.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
|
| |  3 answer | Add comment |
|
| Updateable cursors FAST PostgreSQL 18:02:15 |
| | We are trying to develop the updateable cursors functionality into
Postgresql. I have given below details of the design and also issues we are
facing. Looking forward to the advice on how to proceed with these issues.
Rgds,
Arul Shaji
1. Introduction
--------------
This is a combined proposal and design document for adding updatable
(insensitive) cursor capability to the PostgreSQL database.
There have already been a couple of previous proposals since 2003 for
implementing this feature so there appears to be community interest in doing
so. This will enable the following constructs to be processed:
UPDATE <table_name> SET value_list WHERE CURRENT OF <cursor_name>
DELETE FROM <table_name> WHERE CURRENT OF <cursor_name>
This has the effect of users being able to update or delete specific rows of
a table, as defined by the row currently fetched into the cursor.
2. Overall Conceptual Design
-----------------------------
The design is considered from the viewpoint of progression of a command
through the various stages of processing, from changes to the file gram.y
to implement the actual grammar changes, through to changes in the Executor
portion of the database architecture.
2.1 Changes to the Grammar
------------------------------
The following changes will be done to the PostgreSQL grammar:
UPDATE statement has the option WHERE CURRENT OF <cursor_name> added
DELETE statement has the option WHERE CURRENT OF <cursor_name> added
The cursor_name data is held in the UpdateStmt and DeleteStmt structures and
contains just the name of the cursor.
The pl/pgsql grammar changes in the same manner.
The word CURRENT will be added to the ScanKeywords array in keywords.c.
2.2 Changes to Affected Data Structures
------------------------------------------
The following data structures are affected by this change:
Portal structure, QueryDesc structure, the UpdateStmt and DeleteStmt
structures
The Portal will contain a list of structures of relation ids and tuple ids
relating to the tuple held in the QueryDesc structure. There will be one
entry in the relation and tuple id list for each entry in the relation-list
of the statement below:
DECLARE <cursor_name> [WITH HOLD] SELECT FOR UPDATE OF <relation-list>
The QueryDesc structure will contain the relation id and the tuple id
relating to the tuple obtained via the FETCH command so that it can be
propagated back to the Portal for storage in the list described above.
The UpdateStmt and DeleteStmt structures have the cursor name added so that
the information is available for use in obtaining the portal structure
related to the cursor previously opened via the DECLARE CURSOR request.
2.3 Changes to the SQL Parser
------------------------------------
At present, although the FOR UPDATE clause of the DECLARE CURSOR command has
been present in the grammar, it causes an error message later in the
processing since cursors are currently not updatable. This now needs to
change. The FOR UPDATE clause has to be valid, but not the FOR SHARE
clause.
The relation names that follow the FOR UPDATE clause will be added to the
rtable in the Query structure and identified by means of the rowMarks array.
In the case of an updatable cursor the FOR SHARE option is not allowed
therefore all entries in the rtable that are identified by the rowMarks array
must relate to tables that are FOR UPDATE.
In the UPDATE or DELETE statements the WHERE CURRENT OF <cursor_name>
clause results in the cursor name being placed in the UpdateStmt or
DeleteStmt structure. During the processing of the functions -
transformDeleteStmt() and transformUpdateStmt() - the cursor name is used to
obtain a pointer to the related Portal structure and the tuple affected by
the current UPDATE or DELETE statement is extracted from the Portal, where it
has been placed as the result of a previous FETCH request. At this point all
the information for the UPDATE or DELETE statement is available so the
statements can be transformed into standard UPDATE or DELETE statements and
sent for re-write/planning/execution as usual.
2.4 Changes to the Optimizer
------------------------------
There is a need to add a TidScan node to planning UPDATE / DELETE statements
where the statements are UPDATE / DELETE at position . This is to enable the
tuple ids of the tuples in the tables relating to the query to be obtained.
There will need to be a new mechanism to achieve this, as at present, a Tid
scan is done only if there is a standard WHERE condition on update or delete
statements to provide Tid qualifier data.
2.5 Changes to the Executor
-------------------------------
There are various options that have been considered for this part of the
enhancement. These are described in the sections below.
We would like to hear opinions on which option is the best way to go or if
none of these is acceptable, any alternate ideas ?
Option 1 MVCC Via Continuous Searching of Database
The Executor is to be changed in the following ways:
1)When the FETCH statement is executed the id of the resulting tuple is
extracted and passed back to the Portal structure to be saved to indicate the
cursor is currently positioned on a tuple.
2)When the UPDATE or DELETE request is executed the tuple id previously
FETCHed is held in the QueryDesc structure so that it can be compared with
the tuple ids returned from the TidScan node processed prior to the actual
UPDATE / DELETE node in the plan. This enables a decision to be made as to
whether the tuple held in the cursor is visible to the UPDATE / DELETE
request according to the rules of concurrency. The result is that, at the
cost of repeatedly searching the database at each UPDATE / DELETE command,
the hash table is no longer required.
This approach has the advantage that there is no hash table held in memory or
on disk so it will not be memory intensive but will be processing intensive.
This is a good one-off solution to the problem and, taken in isolation is
probably the best approach. However, if one considers the method(s) used in
other areas of PostgreSQL, it is probably not the best solution. This option
will probably not be used further.
Option 2 MVCC via New Snapshot
The executor can be changed by adding a new kind of snapshot that is
specifically used for identifying if a given tuple, retrieved from the
database during an update or delete statement should be visible during the
current transaction.
This approach requires a new kind of snapshot (this idea was used by Gavin
for a previous updatable cursor patch but objections were raised.)
Option 3 MVCC Via Hash Table in Memory
The executor can be changed by saving into a hash table and comparing each
tuple in the cursor with that set to check if the tuple should be visible.
This approach has the advantage that it will be quick. It has the
disadvantage that, since the hash table will contain all the tuples of the
table being checked that it may use all local memory for a large table.
Option 4 MVCC Via Hash Table on Disk
When the UPDATE or DELETE request is executed the first time the Tid scan
database retrieval will be done first. At this time the tuple id of each row
in the table to be updated by the request will be available in the executor.
These tuple ids need to be stored in a hash table that is stored to disk, as,
if the table is large there could be a huge number of tuple ids. This data is
then available for comparison with the individual tuple to be updated or
deleted to check if it should be processed. The hash table will exist for the
duration of the transaction, from BEGIN to END (or ABORT).
The hash table is then used to identify if the tuple should be visible during
the current transaction. If the tuple should be visible then the update or
delete proceeds as usual.
This approach has the advantage that it will use little memory but will be
relatively slow as the data has to be accessed from disk.
Option 5 Store Tuple Id in Snapshot.
The Snapshot structure can be changed to include the tuple id. This enables
the current state of the tuple to be identified with respect to the current
transaction.
The tuple id, as identified in the cursor at the point where the
DELETE/UPDATE statement is being processed, can use the snapshot to identify
if the tuple should be visible in the context of the current transaction.
2.6 Changes to the Catalog
----------------------------
The Catalog needs to reflect changes introduced by the updatable cursor
implementation. A boolean attribute is_for_update is to be added to the
pg_cursors implementation. It will define that the cursor is for update
(value is FALSE) or for share (value is TRUE, the default value).
3 Design Assumptions
----------------------------
The following design assumptions are made:
As PostgreSQL8.2 does not support the SENSITIVE cursor option the tuples
contained in a cursor can never be updated so these tuples will always appear
in their original form as at the start of the transaction. This is in
breach of the SQL2003 Standard as described in 5WD-02-Foundation-2003-09.pdf,
p 810. The standard requires the updatable cursor to be declared as sensitive.
With respect to nested transactions In PostgreSQL nested transactions are
implemented by defining save points via the keyword SAVEPOINT. A ROLLBACK
TO SAVEPOINT rolls back the database contents to the last savepoint in this
transaction or the begin statement, whichever is closer.
It is assumed that the FETCH statement is used to return only a single row
into the cursor with each command when the cursor is updatable.
According to the SQL2003 Standard Update and Delete statements may contain
only a single base table.
The DECLARE CURSOR statement is supposed to use column level locking, but
PostgreSQL supports only row level locking. The result of this is that the
column list that the standard requires DECLARE <cursor_name> SELECT FOR
UPDATE OF column-list becomes a relation (table) list.
This is an email from Fujitsu Australia Software Technology Pty Ltd, ABN 27 003 693 481. It is confidential to the ordinary user of the email address to which it was addressed and may contain copyright and/or legally privileged information. No one else may read, print, store, copy or forward all or any of it or its attachments. If you receive this email in error, please return to sender. Thank you.
If you do not wish to receive commercial email messages from Fujitsu Australia Software Technology Pty Ltd, please email unsubscribe@fast.fujitsu.com.au
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
|
| | 17 answers | Add comment |
|
| Free space management within heap page Pavan Deolasee 17:48:33 |
| | I am thinking that maintaining fragmented free space within a heap page
might be a good idea. It would help us to reuse the free space ASAP without
waiting for a vacuum run on the page. This in turn will lead to lesser heap
bloats and also increase the probability of placing updated tuple in the
same heap page as the original one.
So during a sequential or index scan, if a tuple is found to be dead, the
corresponding line pointer is marked "unused" and the space is returned to a
free list. This free list is maintained within the page. A linked-list can
be used for this purpose and the special area of the heap-page can be used
to track the fragment list. We can maintain some additional information
about the fragmented space such as, total_free_space, max_fragment_size,
num_of_fragments etc in the special area.
During UPDATEs, if we find that there is no free space in the block, the
fragment list is searched (either first-fit or best-fit), the required space
is consumed and the remaining space is returned to the free list.
We might not be able to reuse the line pointers because indexes may have
references to it. All such line pointers will be freed when the page is
vacuumed during the regular vacuum.
Thanks,
Pavan
EnterpriseDB http://www.enterprisedb.com
|
| | 18 answers | Add comment |
|
| Compiling on JDK 6 Frank Spies 17:23:29 |
| | Hi all,
i tried to compile the jdbc driver on jdk 6. That did not work, several
methods are not implemented. Is there a roadmap when this will be
implemented? It was quite easy to make the code compile, by just
throwing exceptions from all unimplemented methods. Shouldn't we do that
to at least have the possibility to compile under jdk 6?
Thanks, Frank
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
|
| | 3 answer | Add comment |
|
| Searching some sites explaing about PosgtreSQL source codes Re-Plore 15:49:45 |
| | Hi, I am now reading PostgreSQL source codes, but i am not familiar to this
codes.
So i am now seraching some sites which explaing about PostgreSQL source
codes, or it's structure.
If you know a good site explaing PostgreSQL's source codes.
Please teach me.
Thanks a lot of your conservation!
|
| | 1 answer | Add comment |
|
| Applet Connectivity - PLEASE help Marc 14:51:15 |
| | OK, I'll say right up front I'm a postgres novice at best.
I've spent quite some time researching this tonight and trying out a few
things to no avail.
The basic question is can an applet connect to a postgres database and
if so how (I need specific details)?
Postgres, the database, the web server and signed applet are all on the
same machine.
I'm using Postgres 8.2 and Java 1.5 w/ the postgresql-8.2-504.jdbc3 jdbc
driver.
The software works when run through my IDE (Eclipse) but not as an
applet in a browser.
I've set listen_addresses = '*' in postgresql.conf and my pg_hba
settings are:
local all all md5
host all all 127.0.0.1/32 trust.
Here's the code I'm using to try to make the connection where
strServer = :jdbc ostgresql://localhost/Arco
strDriver = org.postgresql.Driver
strUser = postgres
strPswd = fred
public DBConnection(Trace trace, JApplet p_applet)
throws Exception
{
URL dbIniURL;
URLConnection urlConn;
BufferedReader in;
int vals = 0;
String nextVal;
String strProp;
String strVal;
String strUser = "";
String strPswd = "";
int pos;
Properties props = new Properties();
try
{
System.out.println("Instantiate DBConnection.");
dbIniURL = new URL(p_applet.getDocumentBase(), "db.ini");
urlConn = dbIniURL.openConnection();
in = new BufferedReader(new
InputStreamReader(urlConn.getInputStream()));
while (vals < 4)
{
nextVal = in.readLine();
vals = vals + 1;
System.out.println("db.ini: " + nextVal.trim());
pos = nextVal.indexOf(":");
if (pos>0)
{
strProp = nextVal.substring(0,pos);
strVal = nextVal.substring(pos+1);
if (strProp.compareToIgnoreCase("Server") == 0)
{
strServer = strVal;
}
else if (strProp.compareToIgnoreCase("Driver") == 0)
{
strDriver = strVal;
}
else if (strProp.compareToIgnoreCase("User") == 0)
{
strUser = strVal;
}
else if (strProp.compareToIgnoreCase("Password") == 0)
{
strPswd = strVal;
}
}
}
drv = (Driver)Class.forName(strDriver).newInstance();
DriverManager.registerDriver(drv);
props.setProperty("user",strUser.trim());
props.setProperty("password",strPswd.trim());
System.out.println("Attempting to connecting to postgres
db...");
dbConn= DriverManager.getConnection(strServer, props);
System.out.println("Connected to postgres db.");
}
catch (IOException ioe)
{
System.out.println("Error trying to connect to postgres db:");
ioe.printStackTrace();
throw new Exception(ioe.getMessage());
}
}
The error I get in the console is:
Java Plug-in 1.6.0
Using JRE version 1.6.0 Java HotSpot(TM) Client VM
User home directory = C:\Documents and Settings\Marc
----------------------------------------------------
c: clear console window
f: finalize objects on finalization queue
g: garbage collect
h: display this help message
l: dump classloader list
m: print memory usage
o: trigger logging
p: reload proxy configuration
q: hide console
r: reload policy configuration
s: dump system and deployment properties
t: dump thread list
v: dump thread stack
x: clear classloader cache
0-5: set trace level to <n>
----------------------------------------------------
Init applet.
Call new DBConnection.
Instantiate DBConnection.
db.ini: Server:jdbcostgresql://localhost/Arco
db.ini: Driver:org.postgresql.Driver
Attempting to connecting to postgres db...
org.postgresql.util.PSQLException: Something unusual has occured to
cause the driver to fail. Please report this exception.
at org.postgresql.Driver.connect(Driver.java:276)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at db.DBConnection.<init>(DBConnection.java:169)
at ui.BaseApplet.init(BaseApplet.java:138)
at sun.applet.AppletPanel.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
Caused by: java.security.AccessControlException: access denied
(java.net.SocketPermission 127.0.0.1:5432 connect,resolve)
at java.security.AccessControlContext.checkPermission(Unknown Source)
at java.security.AccessController.checkPermission(Unknown Source)
at java.lang.SecurityManager.checkPermission(Unknown Source)
at java.lang.SecurityManager.checkConnect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at java.net.Socket.<init>(Unknown Source)
at java.net.Socket.<init>(Unknown Source)
at org.postgresql.core.PGStream.<init>(PGStream.java:59)
at
org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:77)
at
org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
at
org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
at
org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
at org.postgresql.jdbc3.Jdbc3Connection.<init>(Jdbc3Connection.java:24)
at org.postgresql.Driver.makeConnection(Driver.java:382)
at org.postgresql.Driver.connect(Driver.java:260)
... 6 more
I think that covers it. I'm pretty wiped out being as I've been working
on this for about 4 hrs now.
Your help is really appreciated!
Thanks,
Marc
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
| | 3 answer | Add comment |
|
| Who is Slony Master/Slave + general questions. Guest 14:50:10 |
| | Hello,
I'm starting to use slony as a redundancy solution for the project
I'm currently working on. Running SuSE Linux 9 where one machine
contains the prime database and the second machine contains the backup
database. The Slony version I'm using is 1.1.2. If some of the issues
have been addressed in the newer version of Slony, please let me know.
I have looked at the Nagios scripts and others and am still left with
questions regarding how to dynamically determine who is slave and who
is master during normal and failover operations. Take a scenario that
you want to check the state of the system without prior knowledge of
the node setup, how would you determine which machine is the prime and
which one is the slave?
Also I'm having issues with the slonik script (below) that is supposed
to handle the failover to the slave in case of master failure. For
some reason it hangs and I was wondering if there are known issues with
it. The test condition I'm working with is: reboot the master, the
slave is supposed to take over.
slonik <<_EOF_
# ----
# This defines which namespace the replication system uses
# ----
cluster name = $CLUSTER;
# ----
# Admin conninfo's are used by the slonik program to connect
# to the node databases. So these are the PQconnectdb arguments
# that connect from the administrators workstation (where
# slonik is executed).
# ----
node 1 admin conninfo = 'dbname=$DBNAME1 host=$HOST1 port=5432
user=$SLONY_USER1';
node 2 admin conninfo = 'dbname=$DBNAME2 host=$HOST2
user=$SLONY_USER2';
# ----
# Node 2 subscribes set 1
# ----
failover ( id = 1, backup node = 2);
_EOF_
Thanks a lot for your help,
Slawek
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|
| | 6 answers | Add comment |
|
| Conferences/UGs in March? Josh Berkus 14:22:57 |
| | All,
I need to take a trip to Norway in March. I'd like to stop off at an Open
Source conference either on the way there or the way back. Anything in
Northern/Western Europe in March? Or should I stop off in London or
Paris just to visit the community there?
--
--Josh
Josh Berkus
PostgreSQL @ Sun
San Francisco
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|
| | 2 answer | Add comment |
|
| copy schema X to schema Y in the same DB Johnf 13:32:44 |
| | Hi,
I would like to copy a schema X to a new schema Y within the same database.
Is this possible?
--
John Fabiani
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 1 answer | Add comment |
|
| [1/2 OFF] Varlena.com inaccessible from .br (Blocked?) Jorge Godoy 12:57:21 |
| |
Hi!
I've always used General Bits from the Varlena website as a source of
information and recommended it for friends worldwide for better using
PostgreSQL.
There's been a while since I could use the website for the last time because
it looks like Brazilian networks are blocked somewhere after routers from
speakeasy.net (220.ge-3-0.er1.sfo1.speakeasy.net from this network where I am
now).
Is this blocking intentional? Will it be suspended sometime in the near
future? I'd really like to continue using it and recommending it, but I can't
proxy all the time or ssh to UNC all the time...
I even thought that the company (Varlena) had closed after trying from several
networks here in Brazil... Today, as a last test, I decided using an account
at UNC to check it and to my surprise it did work...
Sorry for this off topic, but I don't have contacts there and I know that
people from there are subscribed here to this mailing list...
TIA,
--
Jorge Godoy <jgodoy@gmail.com>
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 13 answers | Add comment |
|
| msvc failure in largeobject regression test Magnus Hagander 12:35:50 |
| | Hi!
I get failures for the largeobject regression tests on my vc++ build. I
don't think this has ever worked, given that those tests are fairly new.
Any quick ideas on what's wrong before I dig deeper?
//Magnus
*** ./expected/largeobject.outTue Jan 23 14:55:25 2007
--- ./results/largeobject.outTue Jan 23 14:56:17 2007
***************
*** 140,147 ****
-- large object
SELECT loread(fd, 36) FROM lotest_stash_values;
loread
! -----------------------------------------------------------------
! AAA\011FBAAAA\011VVVVxx\0122513\01132\0111\0111\0113\01113\0111
(1 row)
SELECT lo_tell(fd) FROM lotest_stash_values;
--- 140,147 ----
-- large object
SELECT loread(fd, 36) FROM lotest_stash_values;
loread
! --------------------------------------------------------------
! 44\011144\0111144\0114144\0119144\01188\01189\011SNAAAA\011F
(1 row)
SELECT lo_tell(fd) FROM lotest_stash_values;
***************
*** 170,177 ****
SELECT loread(fd, 36) FROM lotest_stash_values;
loread
! -----------------------------------------------------
! AAA\011FBAAAAabcdefghijklmnop1\0111\0113\01113\0111
(1 row)
SELECT lo_close(fd) FROM lotest_stash_values;
--- 170,177 ----
SELECT loread(fd, 36) FROM lotest_stash_values;
loread
! --------------------------------------------------
! 44\011144\011114abcdefghijklmnop9\011SNAAAA\011F
(1 row)
SELECT lo_close(fd) FROM lotest_stash_values;
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
|
| | 5 answers | Add comment |
|
| Numeric to integer Type conversion Saravanan Bellan 11:58:58 |
| | We have database table with the following columns,
foo
------------------
name VARCHAR(20)
bar NUMERIC(20,0)
We were running version 7.2.1 until now.
The following SQL used to work fine in 7.2.1,
SELECT name FROM foo WHERE (bar & 64) <> 0;
Now we upgraded to version 8.1.5 and getting the error,
ERROR: operator does not exist: numeric & integer
HINT: No operator matches the given name and argument type(s). You may
need to add explicit type casts.
NUMERIC(20,0) is probably not the best way to define a column to be used
for bit arithmetic, but we cant change the column type because of
legacy.
Is there anyway I can get the existing SQL to work without any changes
on the application side.
Thanks,
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
| | 1 answer | Add comment |
|
| NETEZZA cursors, anyone? Pankaj_wolfhunter@Yahoo.Co.In 10:13:02 |
| | Greetings,
How can we use cursors in NETEZZA?
Can anyone please show me a simple example here.
I am asking about netezza is because it uses postgresql.
and I cant find any NETEZZA group here.
Any help would be appreciated
TIA
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
|
| | 1 answer | Add comment |
|
| Postgresql.conf Laurent Manchon 09:08:51 |
| | Hi,
I have a slow response of my PostgreSQL database 7.4 using this query below
on a table with 800000 rows:
select count(*)from tbl;
PostgreSQL return result in 28 sec every time.
although MS-SQL return result in 0.02 sec every time.
My server is a DELL PowerEdge 2600 with bi-processor Xeon at 3.2 Ghz
with 3GBytes RAM
My PostgreSQL Conf is
*********************
log_connections = yes
syslog = 2
effective_cache_size = 50000
sort_mem = 10000
max_connections = 200
shared_buffers = 3000
vacuum_mem = 32000
wal_buffers = 8
max_fsm_pages = 2000
max_fsm_relations = 100
Can you tell me is there a way to increase performance ?
Thank you
+-----------------------------------------------------+
| Laurent Manchon |
| Email: lmanchon@univ-montp2.fr |
+-----------------------------------------------------+
|
| | 11 answers | Add comment |
|
| Calling all SoCal PostgreSQL users! Josh Berkus 06:02:13 |
| | PG community of Southern California:
PostgreSQL will have a booth at Southern California Linux Expo (SCALE5x) in
three weeks. The booth will be run by Gavin Roy and Daniel Ceregatti of
our LA community, and David Fetter and I will be helping staff it.
http://www.socallinuxexpo.org/scale5x/
It would be great if other members of our Southern California community
helped us staff the booth and the show, or at least dropped by to say hi
and hang out. Also, it would be cool to have dinner or a party after the
show ... who knows, this could even be the start of an LAPUG.
--
--Josh
Josh Berkus
PostgreSQL @ Sun
San Francisco
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
|
| | 2 answer | Add comment |
|
| dump desde dentro de una base Fernando De Pedro 05:15:04 |
| | Estimados,
Disculpen por la pregunta pero con postgres puedo sacar un dump desde dentro de la base y obtener lo mismo que con pg_dump
Gracias Saludos
---------------------------------
Don't be flakey. Get Yahoo! Mail for Mobile and
always stay connected to friends. |
| | 1 answer | Add comment |
|
| Re: tripping an assert in 8.1.6 (more info) Brian Hurt 04:31:54 |
| | More info on that assert I've hit.
Compile 8.1.6 with configuration options:
./configure --with-perl --enable-debug --enable-cassert
(not sure if --perl is relevent or not, I think not).
This is on Fedora Core 5 on x86-32.
Execute, on a fresh database, the following sql, to recreate the bug:
CREATE TABLE foo
(
some_data VARCHAR(32)
, row_date DATE
);
CREATE TABLE bar
(
some_data VARCHAR(32)
, row_date DATE
);
CREATE TABLE quux
(
some_data VARCHAR(32)
, more_data VARCHAR(32)
);
CREATE OR REPLACE VIEW bazz AS
SELECT
('bar: ' || bar.row_date) :: TEXT AS action,
quux.more_data AS more_data,
bar.row_date AS row_date
FROM
bar
JOIN quux
ON bar.some_data = quux.some_data
UNION ALL
SELECT
('foo: ' || foo.row_date) :: TEXT AS action,
quux.more_data AS more_data,
foo.row_date AS row_date
FROM
foo
JOIN quux
ON foo.some_data = quux.some_data
;
SELECT action, more_data FROM bazz;
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
|
| | 6 answers | Add comment |
|
| psql substitution variables Merlin Moncure 04:17:39 |
| | can anybody think of of a way to sneak these into dollar quoted
strings for substitution into create function?
would i would ideally like to do is (from inside psql shell)
\set foo 500
create function bar() returns int as
$$
declare
baz int default :foo;
[...]
that would eliminate the need for using the c preprocessor (or similar
tool) in some cases that require substitution in that way and (best of
all) allow psql to grok pre defined constants defined externally.
merlin
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
|
| | 2 answer | Add comment |
|
| Calling all SoCal PostgreSQL Users! Josh Berkus 00:43:38 |
| | PG community of Southern California:
PostgreSQL will have a booth at Southern California Linux Expo (SCALE5x) in
three weeks. The booth will be run by Gavin Roy and Daniel Ceregatti of
our LA community, and David Fetter and I will be helping staff it.
http://www.socallinuxexpo.org/scale5x/
It would be great if other members of our Southern California community
helped us staff the booth and the show, or at least dropped by to say hi
and hang out. Also, it would be cool to have dinner or a party after the
show ... who knows, this could even be the start of an LAPUG.
--
--Josh
Josh Berkus
PostgreSQL @ Sun
San Francisco
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | Add comment |
|
| guc fallback to default Joachim Wieland 00:31:57 |
| | I'm working again on the patch for making guc variables fall back to their
default value if they get removed (or commented) in the configuration file.
There is still an issue with custom variables that needs discussion.
Remember that for regular variables we have the following semantics:
BEGIN;
SET enable_seqscan TO off;
COMMIT;
The effect of the commit on the variable is that the variable is set to the
specified value from then on in that session (outside of the transaction).
This is also valid for custom variables. But those can be removed from the
configuration file while all other variables can not (all other variables
fall back to some default value).
Imagine the following example:
Configuration file:
custom_variable_classes = "foo"
foo.var = 3
In a session we do:
BEGIN;
SET foo.var TO 5;
With the transaction still being open, we remove the definition of foo.var
from the configuration file and send SIGHUP.
Then we commit the transaction:
COMMIT;
So what should happen?
Interpretation 1:
foo.var got deleted. COMMIT can not assure that the value of
foo.var gets applied, because foo.var does not exist anymore.
The transaction fails.
Interpretation 2:
The foo.var variable from the configuration file got deleted but the
SET command in the transaction defines a new variable which is
valid, because we still have custom_variable_classes = "foo". The
transaction succeeds.
The second interpretation is based on the fact that you can create a custom
variable by just assigning a value to it. So if you have
custom_variable_classes = "foo", foo.<anythinghere> is a valid variable.
Actually I think we could go either way, it seems to be a really rare corner
case. I'm fine with either way.
Note that if we deleted the line with "custom_variable_classes = foo" from
the previous example as well, it is clear that the transaction should fail.
Joachim
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
|
| | Add comment |
|
| select based on multi-column primary keys Mawrya 00:26:28 |
| | I have set up a table with a multi-column primary key constraint:
CREATE TABLE iopoints
(
enclosureid numeric(3) NOT NULL,
pointid char(4) NOT NULL,
equipmentgroup varchar(64) NOT NULL,
deviceid varchar(8),
devicetype varchar(24),
operationdesc varchar(64) NOT NULL,
entrytime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with
time zone,
systemid numeric(3) NOT NULL,
CONSTRAINT "ID" PRIMARY KEY (systemid, enclosureid, pointid)
)
WITHOUT OIDS;
If I had a row in the table where systemid=123, enclosureid=ab,
pointid=56, I would have a Primary Key ("ID") of 123ab56 for that row.
I now want to run a select based on the Primary Key, something like:
SELECT * FROM iopoints WHERE ID = 123ab56
Is something like this even possible? Or am I forced to do:
SELECT * FROM iopoints WHERE systemid=123 AND enclosureid=ab AND pointid=56
I have searched high and low but can not find a syntax example of how to
select based on a multi-column primary key, any tips?
Thanks,
mawrya
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
|
| | 6 answers | Add comment |
|
| Problems compiling from source George Weaver 00:18:36 |
| | Hi all,
I have installed version 8.2.1 and downloaded the source tree in order to compile a number of C-functions.
I have been compiling from source for contrib files and c-functions since version 7.3 without problem.
I downloaded postgresql-base-8.2.1.tar.gz and unzipped it into the PostgreSQL Program Files directory.
I ran configure without problem, but when I run make I get the following error:
$ make
make -C doc all
make[1]: Entering directory `/c/Program Files/PostgreSQL/postgresql-8.2.1/doc'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/c/Program Files/PostgreSQL/postgresql-8.2.1/doc'
make -C src all
make[1]: Entering directory `/c/Program Files/PostgreSQL/postgresql-8.2.1/src'
make -C port all
make[2]: Entering directory `/c/Program Files/PostgreSQL/postgresql-8.2.1/src/port'
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -fno-strict-aliasing -I../../src/port -DFRONTEND -I../../src/include - I./src/include/port/win32 -DEXEC_BACKEND -c -o crypt.o crypt.c
In file included from ../../src/include/c.h:826,
from crypt.c:44:
../../src/include/port.h:17:19: netdb.h: No such file or directory
../../src/include/port.h:18:17: pwd.h: No such file or directory
../../src/include/port.h:333:24: netinet/in.h: No such file or directory
../../src/include/port.h:334:23: arpa/inet.h: No such file or directory
make[2]: *** [crypt.o] Error 1
make[2]: Leaving directory `/c/Program Files/PostgreSQL/postgresql-8.2.1/src/port'
make[1]: *** [all] Error 2
make[1]: Leaving directory `/c/Program Files/PostgreSQL/postgresql-8.2.1/src'
make: *** [all] Error 2
The files that cannot be found are in the source tree.
Ths has me stumped.
Am I forgetting something obvious??
Thanks,
George |
| | 3 answer | Add comment |
Tuesday, 23 January 2007
|
| IPC resource managements on Solaris 10 Michael Brusser 22:06:25 |
| | The management of the IPC resources on Solaris 10 has changed
(it now involves the /etc/project file)
I wonder if someone can point me to a white-paper or any other
information
for configuring shared resources (semaphores, shared memory, etc) for
Postgresql on Solaris 10.
Thanks,
Michael.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
|
| | Add comment |
|
| "tupdesc reference is not owned by resource owner Portal" issue
in 8.2 and -HEAD Stefan Kaltenbrunner 21:30:57 |
| | The following testcase(extracted from a much much larger production code
sample) results in
WARNING: TupleDesc reference leak: TupleDesc 0xb3573b88 (2249,1) still
referenced
CONTEXT: PL/pgSQL function "foo" line 4 at block variables initialization
ERROR: tupdesc reference 0xb3573b88 is not owned by resource owner Portal
CONTEXT: PL/pgSQL function "foo" while casting return value to
function's return type
on 8.2 and -HEAD.
8.1 seems to work fine.
Stefan
CREATE OR REPLACE FUNCTION public.foo() RETURNS INTEGER AS $$
DECLARE
v_var INTEGER;
BEGIN
BEGIN
v_var := (bar()).error_code;
EXCEPTION WHEN others THEN
RETURN 0;
END;
RETURN 0;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION public.bar(OUT error_code INTEGER, OUT new_id
INTEGER) RETURNS RECORD AS $$
BEGIN
error_code := 1;
new_id := 1;
RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM public.foo();
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
| | 1 answer | Add comment |
|
| Password encryption method Bertram Scharpf 20:55:08 |
| | Hi,
looking at the source code I find out that this works:
sandbox=# create role joe login password 'verysecret';
CREATE ROLE
sandbox=# create function validate_user_8_1(text,text) returns boolean immutable language 'sql' as $$ select 'md5'||md5($2||$1) = rolpassword from pg_authid where rolname=$1; $$;
CREATE FUNCTION
sandbox=# select validate_user_8_1('joe','verysecret');
validate_user_8_1
-------------------
t
(1 Zeile)
May I rely on this in future versions or are there more
sophisticated ways to do it?
Thanks in advance,
Bertram
--
Bertram Scharpf
Stuttgart, Deutschland/Germany
http://www.bertram-scharpf.de
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
| | 12 answers | Add comment |
|
